SQL Server数据字段拆分,合并
ALTER FUNCTION [dbo].[Img_split]
( @str nvarchar(1024) ,
@fengefu nvarchar(20) ,
@addchar nvarchar(100)
)
RETURNS @table TABLE(id INT,val NVARCHAR(500))
AS
BEGIN
DECLARE @index INT,@startsplit INT,@id INT,@result NVARCHAR(500)
SELECT @index=CHARINDEX(@fengefu,@str),@startsplit=1,@id=1
IF @index=0
BEGIN
SELECT @result = @addchar + @str
INSERT INTO @table VALUES (@id, @result)
END
ELSE
BEGIN
WHILE @index>0
BEGIN
IF @id>1
BEGIN
SELECT @startsplit=@index+LEN(@fengefu)
SELECT @index=CHARINDEX(@fengefu,@str,@startsplit)
END
IF @index>0
BEGIN
SELECT @result = @addchar + SUBSTRING(@str,@startsplit,@index-@startsplit)
INSERT INTO @table VALUES (@id, @result)
END
ELSE
BEGIN
UPDATE @table SET val=val+','+ @addchar+SUBSTRING(@str,@startsplit,LEN(@str)-@startsplit+1) WHERE id=1
END
SELECT @id=@id+1
END
END
RETURN
END