RSS 2.0
Sign In
# Tuesday, February 20, 2007

This task is already discussed many times. SQL Server 2005 allows to create an inline function that splits such a string. The logic of such a function is self explanatory, which also hints that SQL syntax became better:

/*
  Returns numbers table.
  Table has a following structure: table(value int not null);
  value is an integer number that contains numbers from 0 to a specified value.
*/

create function dbo.Numbers
(    
  /* Number of rows to return. */
  @count int
)
returns table
as
return
with numbers(value) as
(
  select 0
  union all
  select value * 2 + 1 from numbers where value < @count / 2
  union all
  select value * 2 + 2 from numbers where value < (@count - 1) / 2
)
select
  row_number() over(order by U.v) value
from
  numbers cross apply (select 0 v) U;

/*
  Splits string using split character.
  Returns a table that contains split positions and split values:
  table(Pos, Value)
*/

create function dbo.Split
(
  /* A string to split. */
  @value nvarchar(max),
  /* An optional split character.*/
  @splitChar nvarchar(max) = N','
)
returns table
as
return
with Bound(Pos) as
(
  select
    Value
  from
    dbo.Numbers(len(@value))
  where
    (Value = 1) or
    (substring(@value, Value - 1, len(@splitChar)) = @splitChar)
),
Word(Pos, Value) as
(
  select
    Bound.Pos,
    substring
    (
      @value,
      Bound.Pos,
      case when Splitter.Pos > 0
        then Splitter.Pos
        else len(@value) + 1
      end - Bound.Pos
    )
  from
    Bound
    cross apply
    (select charindex(@splitChar, @value, Pos) Pos) Splitter
)
select Pos, Value from Word;

Test:

declare @s nvarchar(max);

set @s = N'ALFKI,BONAP,CACTU,FRANK';

select Value from System.Split(@s, default) order by Pos;

See also: Arrays and Lists in SQL Server, Numbers table in SQL Server 2005, Parade of numbers

All comments require the approval of the site owner before being displayed.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, b, blockquote@cite, em, i, strike, strong, sub, super, u) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview
Archive
<February 2007>
SunMonTueWedThuFriSat
28293031123
45678910
11121314151617
18192021222324
25262728123
45678910
Statistics
Total Posts: 387
This Year: 3
This Month: 0
This Week: 0
Comments: 2053
Locations of visitors to this page
Disclaimer
The opinions expressed herein are our own personal opinions and do not represent our employer's view in anyway.

© 2024, Nesterovsky bros
All Content © 2024, Nesterovsky bros
DasBlog theme 'Business' created by Christoph De Baene (delarou)