I want to split a column of strings say "99 crystal springs road" and get only 2 words (99 and crystal) respectively and update 99 to one column and crystal to another column of another table. How can I do it using charindex and substring?
-
1What are the rules as to why you only want 99 and crystal? Because they're the first two words?kaj– kaj2012-02-29 23:38:43 +00:00Commented Feb 29, 2012 at 23:38
Add a comment
|
3 Answers
Here is some sample code on how to do it ...
First, create this function:
CREATE FUNCTION [dbo].[GetStringPart]
(@fullString varchar(200), @pos tinyint)
RETURNS VARCHAR(200) -- return_data_type.
AS
BEGIN
IF @pos IS NULL OR @pos <= 0
SET @pos = 1
declare @secondPart varchar(200),@firstPart varchar(200),@output varchar(200)
declare @firstSpace int, @secondSpace int
set @firstSpace = CHARINDEX(' ', @fullString)
IF @firstSpace <= 0
RETURN ''
ELSE IF @pos = 1
BEGIN
SET @output = LTRIM(RTRIM(SUBSTRING(@fullString, 1, @firstSpace)))
END
ELSE
BEGIN
SET @secondSpace = CHARINDEX(' ', @fullString, CHARINDEX(' ', @fullString)+1) - CHARINDEX(' ', @fullString)+1
IF @secondSpace <= 0
SET @secondSpace = LEN(@fullString) - @firstSpace + 1
SET @output = LTRIM(RTRIM(SUBSTRING(@fullString, @firstSpace, @secondSpace)))
END
RETURN @Output
END
GO
Then you can use it like this:
declare @origTable table(name varchar(100))
insert into @origTable (name) values ('99 crystal springs road')
declare @newTable table(col1 varchar(100), col2 varchar(100))
INSERT INTO @newTable (col1, col2)
SELECT dbo.GetStringPart(name, 1), dbo.GetStringPart(name, 2) FROM @origTable
select * from @newTable
3 Comments
rsDesigner
I get "Invalid length parameter passed to the LEFT or SUBSTRING function."
rsDesigner
I have a table with records of addresses like above. split the address and get only first two words and dump into another table. I hope I am explaining this issue correctly.
Donald Hansen
The reason for that error was probably because you didn't have 2 spaces in every row. I would create a function and then call that function. The function can have some logic to check for that second space. I revised my answer above.
Assuming that you are selecting "99" and "crystal" just because they're the first two words...
You can do this in a single step but for ease of reading the solution I've separated it out
declare @sourceAddresses table
(
address varchar(100)
)
declare @split table
(
address varchar(100),
firstDelimiter int,
secondDelimiter int
)
declare @table table
(
part1 varchar(20),
part2 varchar(20)
)
insert into @sourceAddresses (address) values ('99 crystal springs road')
insert into @sourceAddresses (address) values ('100 elsewhere road')
insert into @split (address, firstDelimiter)
select address, charindex(' ', address)
from @sourceAddresses
update @split
set secondDelimiter = charindex(' ', address, (firstDelimiter+1))
where firstDelimiter > -1
insert into @table (part1, part2)
select substring(address, 0, firstDelimiter), substring(address, (firstDelimiter+1), (secondDelimiter-firstDelimiter))
from @split
where firstDelimiter > -1
and secondDelimiter > -1
select * from @table
3 Comments
kaj
Updated solution to use a source table of addresses rather than a single variable address
rsDesigner
This works for the example but when I use actual data which is of 90 characters in length, it throws Invalide length passed to the LEFT or SUBSTRING function. Is it because of NULLS or EMPTY probably? OR If there are only two words like "9 street"?
kaj
Yes you need to cater for each edge case - null, no spaces, single spaces, multiple consecutive spaces (hint:ltrim and rtrim) etc. just use the sample code and insert one additional case at a time and get that working as well. When doing the actual insertion you can check that you have both delimiter positions calculated - I'll update the answer
Maybe something like this:
First create a function that gets part of the strings:
CREATE FUNCTION dbo.GetStringPart (@sep char(1), @s varchar(512),@pos int)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @output VARCHAR(200)
;WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT
@output=SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END)
FROM Pieces
WHERE pn=@pos
RETURN @Output
END
GO
Then you can easy do this:
DECLARE @origalTable TABLE(name VARCHAR(100))
INSERT INTO @origalTable
VALUES('99 crystal springs road')
DECLARE @newTable TABLE(col1 VARCHAR(100), col2 VARCHAR(100))
INSERT INTO @newTable (col1, col2)
SELECT dbo.GetStringPart(' ',name, 1), dbo.GetStringPart(' ',name, 2) FROM @origalTable
SELECT * FROM @newTable
DROP FUNCTION dbo.GetStringPart
