Passing delimeted string into table

• balbirsinghsodhi

  SSCrazy Eights

  Points: 9210

  More actions

  August 15, 2002 at 5:53 pm

  #105250

  Can somebody help me creating function in SQL Server .

  I am trying to store data from delimeted string to a table.

  for example.

  declare @a varchar(3000)

  set @a='rajeev,sanjay,bob'

  I want to store in a table which have only one field.

  Storage should be like this.

  name

  ----

  rajeev

  sanjay

  bob

  Thanks in advance

• Deuce

  SSCrazy

  Points: 2712

  More actions

  August 16, 2002 at 2:43 pm

  #434744

  Thanks. I've needed an excuse to write a split like function. One of the handy things I miss from VB in T-SQL.

  This function will return a table. You need to pass it a split character, i.e. ',' '|' ':' etc and a string to split.

  For example:

  select * from dbo.Split(',', 'rajeev,sanjay,bob')

  Here's the function:

  Create Function Split(@splitchar char(1), @list varchar(3000))

  Returns @out TABLE

  (

  Field varchar(3000)

  )

  as

  Begin

  /*

  declare @splitchar char(1)

  declare @list varchar(3000)

  set @splitchar = ','

  set @list = 'rajeev,sanjay,bob '

  */

  declare @x int

  set @x = 1

  while @x > 0

  begin

  set @x = patindex('%' + @splitchar + '%', @list) - 1

  if @x > 0

  begin

  insert into @out (field) select substring(@list, 1, @x)

  set @list = ltrim(rtrim(substring(@list, @x + 2, len(@list) - @x)))

  end

  if @x < 1

  insert into @out (field) select @list

  end

  Return

  End

• balbirsinghsodhi

  SSCrazy Eights

  Points: 9210

  More actions

  August 21, 2002 at 12:15 pm

  #434952

  Thanks buddy