i have this function:

CREATE FUNCTION user.split (
@list varchar(4000),
@delimiter varchar(5))
RETURNS @listtable TABLE (
  value varchar(100))
AS
BEGIN
WHILE (CHARINDEX(@delimiter,@list)>0)
BEGIN
  INSERT INTO @listtable (value)
  SELECT value =
LTRIM(RTRIM(SUBSTRING(@list,1,CHARINDEX(@delimiter,@list)-1)))
   SET @list =
SUBSTRING(@list,CHARINDEX(@delimiter,@list)+LEN(@delimiter),LEN(@list))
  END

  INSERT INTO @listtable (value)
  SELECT value = LTRIM(RTRIM(@list))
  RETURN
END

which turns a comma separated string into a table. it works just fine
when i use it on a simple statement, such as:

SELECT * FROM split('1,2,3,4,5',',')

produces the resultset:

1
2
3
4
5

the problem is when i want to use it with dynamic values, such as a
column in a table that has (unfortunately) comma separated values. this
effort is part of the goal of normalizing these comma separated values.
right now it looks kind of like so:

CREATE TABLE thetable (
thetableid int identity (1, 1) primary key not null,
listofints varchar(50) not null -- this is the comma separated list of
int values
)

INSERT INTO thetable VALUES (1, '1,2,3,4,5')

SELECT * FROM split(SELECT listofints FROM thetable WHERE thetableid =
1,',')

but the split function won't work on the set based operation to get the
commaseparated value. is there any way that i can use the user defined
function in a set based operation, as i'm trying to do?

thanks for any help,

jason

jason (iae***@yahoo.com) writes:
Show quoteHide quoteIf you are on SQL 2005 you can use the new CROSS APPLY operator. If you
are on SQL 2000, it's cursor time, I'm afraid.

Instead of trying to reuse the UDF with an expression as the parameter, try
reusing the parsing logic. One common approach is to use a table of
sequentially incrementing numbers -- parsing strings are a breeze with the
SUBSTRING & CHARINDEX functions.

Another option is to use a table valued UDF rather than a scalar one. This
will allow you to use the function inline while using it in the main query.
There are different ways you could do this, one method can be found at:
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e?dmode=source
( See #4 )

Well, that is the wrong url :-(

The URL worked for me.  Of course #4 is very similar to what I just
posted in this thread.  ;)

SK

Anith Sen wrote:

Show quoteHide quote

http://groups.google.com/groups?hl=en&q=Anith+parse+Nbrs

For example using Steve's sample table:

SELECT thetableid,
       SUBSTRING( listofints , Nbr,
            CHARINDEX( ',', listofints + ',', Nbr ) - Nbr )
  FROM Seq, thetable
WHERE SUBSTRING( ',' + listofints, Nbr, 1 ) = ','
   AND Nbr < LEN( listofints ) + 1 ;

You can in SQL 2005 using APPLY.

In SQL 2000 you'll have to go through each row and parse the string then
insert the values as new rows into a normalized table. Cursor is an option if
this is a one-deal process.


ML

---
http://milambda.blogspot.com/

Jason,

You can do this all in one shot without cursors, but
you will want to take a different approach to splitting
the lists.  Instead of iterating through the commas, express
the comma positions as a derived table by joining your table
to a permanent table of integers.

Here's a repro script.  It may or may not be faster than
using a cursor, but once you understand it, it may be easier
to maintain.

--A table of integers is needed
--Create this once and keep it around
create table Seq (
  Nbr int not null
)

insert into Seq
select top 4001 0
from Northwind..[Order Details]
cross join (select 1 as n union all select 2) X

declare @i int
set @i = -1
update Seq
  set @i = Nbr = @i + 1

alter table Seq add constraint pk_Seq primary key (Nbr)
--table Seq created
go


--Your table
CREATE TABLE thetable (
thetableid int primary key not null,
listofints varchar(500) not null -- comma separated list
)

INSERT INTO thetable VALUES (1, '1,2,3,4,5')
INSERT INTO thetable VALUES (2, '11,12,3,14,54')
INSERT INTO thetable VALUES (3, '1')
INSERT INTO thetable VALUES (4, '123,234,345,456'+replicate(',77',100))
GO

-- This query gives you all the items from all the lists
select distinct -- you may or may not want distinct
  thetableid,
  ltrim(rtrim(
    substring(listofints,
       commaPos+1,
        charindex(',', listofints, commaPos+1) - (commaPos+1))))
from (
  select
    thetableid,
    replace(rtrim(','+ltrim(listofints))+',', ',,', ',') as listofints
  from thetable
) as T
join (select Nbr as commaPos from Seq) S
on substring(listofints,commaPos,1) = ','
and commaPos < len(listofints)
go

-- DROP TABLE Seq, thetable

-- Steve Kass
-- Drew University



jason wrote:

Show quoteHide quote

thought i'd post the reply here as well, in case others have the answer

jason (iae***@yahoo.com) writes:
Yes, that is the purpose of Seq.

But if you don't have Northwind, that would indicate that you are on
SQL 2005, in which case you can use the CROSS APPLY operator with
your function:

   SELECT t.thetableid, s.value
   FROM   thetable AS t
   CROSS  APPLY split(t.listofints, ',') AS s
   ORDER BY t.thetableid, s.value

Column X is same, column Y is different?
Imbedded subroutine prefix error
NULL in Joins
Memory problems using Sql Server 2000
Joing two tables but avoid cartesian product