tags:

views:

12553

answers:

8
+21  Q: 

Split String in SQL

Using SQL Server 2005, how do I split a string so I can access item x.

For example, take the string "Hello John Smith". How can I split the string by a space and access item 1 which should return "John"?

+10  A: 

You may find this CodeProject solution helpful.

Jonesinator
why `SET @p_SourceText = RTRIM( LTRIM( @p_SourceText)) SET @w_Length = DATALENGTH( RTRIM( LTRIM( @p_SourceText)))`and not `SET @p_SourceText = RTRIM( LTRIM( @p_SourceText)) SET @w_Length = DATALENGTH( @p_SourceText)`?
Beth
A: 

Well, mine isn't all that simpler, but here is the code I use to split a comma-delimited input variable into individual values and put it into a Table Variable. I'm sure you could modify this slightly to split based on a space and then to a basic SELECT query against that table variable to get your results.

-- Create temporary table to parse the list of accounting cycles.
DECLARE @tblAccountingCycles table
(
AccountingCycle varchar(10)
)

DECLARE @vchAccountingCycle varchar(10)
DECLARE @intPosition int

SET @vchAccountingCycleIDs = LTRIM(RTRIM(@vchAccountingCycleIDs)) + ','
SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)

IF REPLACE(@vchAccountingCycleIDs, ',', '') <> ''
BEGIN
WHILE @intPosition > 0
BEGIN
SET @vchAccountingCycle = LTRIM(RTRIM(LEFT(@vchAccountingCycleIDs, @intPosition - 1)))
IF @vchAccountingCycle <> ''
BEGIN
INSERT INTO @tblAccountingCycles (AccountingCycle) VALUES (@vchAccountingCycle)
END
SET @vchAccountingCycleIDs = RIGHT(@vchAccountingCycleIDs, LEN(@vchAccountingCycleIDs) - @intPosition)
SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)

END
END

The concept is pretty much the same. One other alternative is to leverage the .NET compatability within SQL Server 2005 itself. You can essentially wight yourself a simple method in .NET that would split the string and then expose that as a stored procedure/function.

Dillie-O
+8  A: 

Here is a UDF which will do it. It will return a table of the delimited values, haven't tried all scenarios on it but your example works fine.


CREATE FUNCTION SplitString
(
-- Add the parameters for the function here
@myString varchar(500),
@deliminator varchar(10)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [varchar](50) NULL
)
AS
BEGIN
Declare @iSpaces int
Declare @part varchar(50)

--initialize spaces
Select @iSpaces = charindex(@deliminator,@myString,0)
While @iSpaces > 0

Begin
Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

Insert Into @ReturnTable(part)
Select @part

Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))


Select @iSpaces = charindex(@deliminator,@myString,0)
end

If len(@myString) > 0
Insert Into @ReturnTable
Select @myString

RETURN
END
GO

You would call it like this:


Select * From SplitString('Hello John Smith',' ')

Edit: Updated solution to handle delimters with a len>1 as in :


select * From SplitString('Hello**John**Smith','**')
brendan
Didn't work for select * from dbo.ethos_SplitString_fn('guy,wicks,was here',',')id part----------- --------------------------------------------------1 guy 2 wick
Guy
watch out with len() as it'll not return correct number if its argument has trailing spaces., e.g. len(' - ') = 2.
Rory
Doesn't work on: select * from dbo.SplitString('foo,foo test,,,,foo',',')
cbp
+1  A: 

Try this:

CREATE function [SplitWordList]
(
@list varchar(8000)
)
returns @t table
(
Word varchar(50) not null,
Position int identity(1,1) not null
)
as begin
declare
@pos int,
@lpos int,
@item varchar(100),
@ignore varchar(100),
@dl int,
@a1 int,
@a2 int,
@z1 int,
@z2 int,
@n1 int,
@n2 int,
@c varchar(1),
@a smallint
select
@a1 = ascii('a'),
@a2 = ascii('A'),
@z1 = ascii('z'),
@z2 = ascii('Z'),
@n1 = ascii('0'),
@n2 = ascii('9')
set @ignore = '''"'
set @pos = 1
set @dl = datalength(@list)
set @lpos = 1
set @item = ''
while (@pos <= @dl) begin
set @c = substring(@list, @pos, 1)
if (@ignore not like '%' + @c + '%') begin
set @a = ascii(@c)
if ((@a >= @a1) and (@a <= @z1))
or ((@a >= @a2) and (@a <= @z2))
or ((@a >= @n1) and (@a <= @n2))
begin
set @item = @item + @c
end else if (@item > '') begin
insert into @t values (@item)
set @item = ''
end
end
set @pos = @pos + 1
end
if (@item > '') begin
insert into @t values (@item)
end
return
end

Test it like this:

select * from SplitWordList('Hello John Smith')
Terrapin
Vikas
+13  A: 

I don't believe SQL Server has a built-in split function, so other than a UDF, the only other answer I know is to hijack the PARSENAME function:

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2) 

PARSENAME takes a string and splits it on the period character. It takes a number as it's second argument, and that number specifies which segment of the string to return (working from back to front).

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3)  --return Hello

Obvious problem is when the string already contains a period. I still think using a UDF is the best way...any other suggestions?

Nathan Bedford
i looked all over for this answer, thanks!!!!!!!!!!
Eric Labashosky
this is pretty clever
Saul Dolgin
Thanks Saul...I should point out that this solution is really a bad solution for real development. PARSENAME only expects four parts, so using a string with more than four parts causes it to return NULL.The UDF solutions are obviously better.
Nathan Bedford
This is a great hack, and also makes me weep that something like this is necessary for something so friggin simple in real languages.
Factor Mystic
+5  A: 

First, create a function (using CTE, common table expression does away with the need for a temp table)

 create function dbo.SplitString 
    (
        @str nvarchar(4000), 
        @separator char(1)
    )
    returns table
    AS
    return (
        with tokens(p, a, b) AS (
            select 
                1, 
                1, 
                charindex(@separator, @str)
            union all
            select
                p + 1, 
                b + 1, 
                charindex(@separator, @str, b + 1)
            from tokens
            where b > 0
        )
        select
            p-1 zeroBasedOccurance,
            substring(
                @str, 
                a, 
                case when b > 0 then b-a ELSE 4000 end) 
            AS s
        from tokens
      )
    GO

Then, use it as any table (or modify it to fit within your existing stored proc) like this.

  select s 
    from dbo.SplitString('Hello John Smith', ' ')
    where zeroBasedOccurance=1
vzczc
+1  A: 

You can leverage a Number table to do the string parsing lightning fast:

    create function [dbo].[ufn_ParseArray]
    ( @Input  nvarchar(4000), 
     @Delimiter char(1) = ',',
     @BaseIdent int
    )
returns table as
return  
    ( select row_number() over (order by n desc) + (@BaseIdent - 1) [i],
       substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s
     from dbo.Number
     where n <= convert(int, len(@Input)) and
       substring(@Delimiter + @Input, n, 1) = @Delimiter
    )

Usage:

declare @Array varchar(500)
set @Array = 'Hello John smith'

select  s 
from    dbo.ufn_ParseArray(@Array, ' ', 0)
where   i = 1
Nathan Skerl
+1  A: 

No code, but read the definitive article on this. All solutions in other answers are flavours of the ones listed in this article: Arrays and Lists in SQL Server 2005 and Beyond

Personally, I've used a Numbers table solution most often because it suits what I have to do...

gbn