Thursday, August 11, 2011

split function and cross apply

create table product(name varchar(20),id varchar(20))

insert into product(name,id)values('sql','1,2,3')
insert into product(name,id)values('oracle','1')
insert into product(name,id)values('db2','2')
insert into product(name,id)values('sybase','3')
insert into product(name,id)values('mysql','4')
insert into product(name,id)values('php','5')
insert into product(name,id)values('dotnet','6')
insert into product(name,id)values('java','7,8')

select * from product

CREATE FUNCTION [dbo].[FPSplit]
(
       @List As Varchar(Max)
)
Returns @Items Table (Item Varchar(Max) NOT NULL)
AS

BEGIN
 Declare @Item As Varchar(Max)
 Declare @Pos As Int

 WHILE DATALENGTH(@List)>0
               BEGIN
                       SET @Pos=CHARINDEX(',',@List)
                       IF @Pos=0
                           SET @Pos=DATALENGTH(@List)+1
                          SET @Item =  LTRIM(RTRIM(LEFT(@List,@Pos-1)))
                       IF @Item<>''
                           INSERT INTO @Items SELECT @Item
                           SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
               END
                                  
 RETURN

END

SELECT name,Item as id FROM product p CROSS APPLY [dbo].[FPSplit](p.id)


No comments:

Post a Comment