Split String in SQL

Question

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"?

Answer 1

You may find this CodeProject solution helpful.

Answer 2

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.

Answer 3

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','**')

Answer 4

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')

Answer 5

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?

Answer 6

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

Answer 7

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

Answer 8

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...