CREATE TABLE tb(VolServiceGroupId varchar(20), VolSGroupNames varchar(50)) INSERT tb VALUES(NULL,'4|拥抱,5|阳光,6|精神预防,7|餐饮业');GOCREATE FUNCTION dbo.f_VolServiceGroupID(@VolServiceGroupId varchar(50)) RETURNS VARCHAR(50) AS BEGIN DECLARE @re VARCHAR(50); SET @re='';
SELECT @re=@re+','+LEFT(VolSGroupNames,CHARINDEX('|',VolSGroupNames)-1) FROM ( SELECT SUBSTRING(@VolServiceGroupId,number, CHARINDEX(',',@VolServiceGroupId+',',number)-number) AS VolSGroupNames FROM master.dbo.spt_values WHERE type='p' AND number BETWEEN 1 AND LEN(@VolServiceGroupId) AND SUBSTRING(','+@VolServiceGroupId,number,1)=',' ) AS A;
RETURN STUFF(@re,1,1,''); END GOUPDATE tb SET VolServiceGroupId=dbo.f_VolServiceGroupID(VolSGroupNames)
--查询SELECT * FROM tb; GODROP TABLE tb; DROP FUNCTION dbo.f_VolServiceGroupID;/* VolServiceGroupId VolSGroupNames -------------------- -------------------------------------------------- 4,5,6,7 4|拥抱,5|阳光,6|精神预防,7|餐饮业(1 行受影响)*/
--利用下边的函数进行处理吧--先用 , 拆分,拆分后再用 | 拆分 ,中间结果写到临时表里,然后再导入你的表 /* 作者:贾桂军 日期:2009-04-01 作用:根据指定拆分字符,对字符串进行拆分 调用:select Split from fun_Split(',','1,2,3') */ CREATE function [dbo].[fun_Split] (@Symbol varchar(100), @SplitStr varchar(8000)) returns @t table(Split varchar(10)) begin declare @i int set @i=charindex(@Symbol,@SplitStr) while @i<>0 begin insert @t values(left(@SplitStr,@i-1)) set @SplitStr=right(@SplitStr,len(@SplitStr)-@i) set @i=charindex(@Symbol,@SplitStr) end insert @t values(@SplitStr) return end
INSERT tb VALUES(NULL,'4|拥抱,5|阳光,6|精神预防,7|餐饮业');GOCREATE FUNCTION dbo.f_VolServiceGroupID(@VolServiceGroupId varchar(50))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @re VARCHAR(50);
SET @re='';
SELECT @re=@re+','+LEFT(VolSGroupNames,CHARINDEX('|',VolSGroupNames)-1)
FROM (
SELECT
SUBSTRING(@VolServiceGroupId,number,
CHARINDEX(',',@VolServiceGroupId+',',number)-number) AS VolSGroupNames
FROM master.dbo.spt_values
WHERE type='p'
AND number BETWEEN 1 AND LEN(@VolServiceGroupId)
AND SUBSTRING(','+@VolServiceGroupId,number,1)=','
) AS A;
RETURN STUFF(@re,1,1,'');
END
GOUPDATE tb SET
VolServiceGroupId=dbo.f_VolServiceGroupID(VolSGroupNames)
--查询SELECT * FROM tb;
GODROP TABLE tb;
DROP FUNCTION dbo.f_VolServiceGroupID;/*
VolServiceGroupId VolSGroupNames
-------------------- --------------------------------------------------
4,5,6,7 4|拥抱,5|阳光,6|精神预防,7|餐饮业(1 行受影响)*/
/*
作者:贾桂军
日期:2009-04-01
作用:根据指定拆分字符,对字符串进行拆分
调用:select Split from fun_Split(',','1,2,3')
*/
CREATE function [dbo].[fun_Split]
(@Symbol varchar(100),
@SplitStr varchar(8000))
returns @t table(Split varchar(10))
begin
declare @i int
set @i=charindex(@Symbol,@SplitStr)
while @i<>0
begin
insert @t values(left(@SplitStr,@i-1))
set @SplitStr=right(@SplitStr,len(@SplitStr)-@i)
set @i=charindex(@Symbol,@SplitStr)
end
insert @t values(@SplitStr)
return
end