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