CODEWITHSUNDEEP
sql-servert-sql
rsDesigner
rsDesigner

Reputation: 23

SQL SERVER 2008 R2 string split

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?

Upvotes: 1

Views: 7632

Answers (3)

Arion
Arion

Reputation: 31239

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

Upvotes: 1

kaj
kaj

Reputation: 5251

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

Upvotes: 1

Donald Hansen
Donald Hansen

Reputation: 111

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

Upvotes: 1

Related Questions