字符串截取有关问题

/*标题：简单数据拆分(version 2.0)作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)时间：2010-05-07地点：重庆航天职业学院描述：有表tb, 如下:id          value----------- -----------1           aa,bb2           aaa,bbb,ccc欲按id,分拆value列, 分拆后结果如下:id          value----------- --------1           aa1           bb2           aaa2           bbb2           ccc*/--1. 旧的解决方法(sql server 2000)create table tb(id int,value varchar(30))insert into tb values(1,'aa,bb')insert into tb values(2,'aaa,bbb,ccc')go--方法1.使用临时表完成SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)FROM tb A, # BWHERE SUBSTRING(',' + A.[value], B.id, 1) = ','DROP TABLE #--方法2.如果数据量小，可不使用临时表select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number) from tb a join master..spt_values  b on b.type='p' and b.number between 1 and len(a.value)where substring(',' + a.value , b.number , 1) = ','--2. 新的解决方法(sql server 2005)create table tb(id int,value varchar(30))insert into tb values(1,'aa,bb')insert into tb values(2,'aaa,bbb,ccc')go--方法1.使用xml完成SELECT A.id, B.value FROM(  SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb) A OUTER APPLY(  SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)) B--方法2.使用CTE完成;with tt as (select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tbunion allselect id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>'')select id,[value] from tt order by id option (MAXRECURSION 0)DROP TABLE tb/*id          value----------- ------------------------------1           aa1           bb2           aaa2           bbb2           ccc(5 行受影响)*/
------解决方案--------------------
SQL code
表a name    num aa      1 bb      2 cc      4 dd      4 ee      2 一条语句实现如下:name aa,bb,cc,dd,ee DECLARE @STR VARCHAR(8000)SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT NAME FROM A)AS TSELECT @STR问题描述：无论是在sql 2000，还是在sql 2005 中,都没有提供字符串的聚合函数，  所以，当我们在处理下列要求时，会比较麻烦：有表tb, 如下：id    value1     aa1     bb2     aaa2     bbb2     ccc需要得到结果：id     values1      aa,bb2      aaa,bbb,ccc即，group by id, 求value 的和（字符串相加）1. 旧的解决方法-- 1. 创建处理函数CREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGIN    DECLARE @r varchar(8000)    SET @r = ''    SELECT @r = @r + ',' + value    FROM tb    WHERE [email protected]    RETURN STUFF(@r, 1, 1, '')ENDGO-- 调用函数SELECt id, values=dbo.f_str(id) FROM tb GROUP BY id-- 2. 新的解决方法-- 示例数据DECLARE @t TABLE(id int, value varchar(10))INSERT @t SELECT 1, 'aa'UNION ALL SELECT 1, 'bb'UNION ALL SELECT 2, 'aaa'UNION ALL SELECT 2, 'bbb'UNION ALL SELECT 2, 'ccc'-- 查询处理SELECT *FROM (SELECT DISTINCT Id FROM @t)AOUTER APPLY(    SELECT [values]= STUFF(REPLACE(REPLACE(            (   SELECT value FROM @t N                WHERE id = A.id                FOR XML AUTO             ), '<N value="', ','), '"/>', ''), 1, 1, ''))N/*--结果id          values----------- ----------------1           aa,bb2           aaa,bbb,ccc(2 行受影响)--*/--各种字符串分函数--3.3.1 使用游标法进行字符串合并处理的示例。--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并处理--定义结果集表变量DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))--定义游标并进行合并处理DECLARE tb CURSOR LOCALFORSELECT col1,col2 FROM tb ORDER BY  col1,col2DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)OPEN tbFETCH tb INTO @col1,@col2SELECT @[email protected],@s=''WHILE @@FETCH_STATUS=0BEGIN    IF @[email protected]_old        SELECT @[email protected]+','+CAST(@col2 as varchar)    ELSE    BEGIN        INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))        SELECT @s=','+CAST(@col2 as varchar),@[email protected]    END    FETCH tb INTO @col1,@col2ENDINSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))CLOSE tbDEALLOCATE tb--显示结果并删除测试数据SELECT * FROM @tDROP TABLE tb/*--结果col1       col2a          1,2b          1,2,3--*/GO--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3GO--合并处理函数CREATE FUNCTION dbo.f_str(@col1 varchar(10))RETURNS varchar(100)ASBEGIN    DECLARE @re varchar(100)    SET @re=''    SELECT @[email protected]+','+CAST(col2 as varchar)    FROM tb    WHERE [email protected]    RETURN(STUFF(@re,1,1,''))ENDGO--调用函数SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1--删除测试DROP TABLE tbDROP FUNCTION f_str/*--结果col1       col2a          1,2b          1,2,3--*/GO--3.3.3 使用临时表实现字符串合并处理的示例--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并处理SELECT col1,col2=CAST(col2 as varchar(100)) INTO #t FROM tbORDER BY col1,col2DECLARE @col1 varchar(10),@col2 varchar(100)UPDATE #t SET     @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,    @col1=col1,    [email protected]SELECT * FROM #t/*--更新处理后的临时表col1       col2a          1a          1,2b          1b          1,2b          1,2,3--*/--得到最终结果SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1/*--结果col1       col2a          1,2b          1,2,3--*/--删除测试DROP TABLE tb,#tGO--3.3.4.1 每组<=2 条记录的合并--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'c',3--合并处理SELECT col1,    col2=CAST(MIN(col2) as varchar)        +CASE             WHEN COUNT(*)=1 THEN ''            ELSE ','+CAST(MAX(col2) as varchar)        ENDFROM tbGROUP BY col1DROP TABLE tb/*--结果col1       col2      ---------- ----------a          1,2b          1,2c          3--*/--3.3.4.2 每组<=3 条记录的合并--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3UNION ALL SELECT 'c',3--合并处理SELECT col1,    col2=CAST(MIN(col2) as varchar)        +CASE             WHEN COUNT(*)=3 THEN ','                +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)            ELSE ''        END        +CASE             WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)            ELSE ''        ENDFROM tb aGROUP BY col1DROP TABLE tb/*--结果col1       col2---------- ------------a          1,2b          1,2,3c          3--*/GOif not object_id('A') is null    drop table AGoCreate table A([id] int,[cname] nvarchar(2))Insert Aselect 1,N'张三' union allselect 2,N'李四' union allselect 3,N'王五' union allselect 4,N'蔡六'Go--> -->  if not object_id('B') is null    drop table BGoCreate table B([id] int,[cname] nvarchar(5))Insert Bselect 1,N'1,2,3' union allselect 2,N'3,4'Gocreate function F_str(@cname nvarchar(100))returns nvarchar(100)asbegin select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',',[email protected]+',')>0return @cnameendgoselect [id],dbo.F_str([cname])[cname] from Bid          cname1           张三,李四,王五2           王五,蔡六(2 個資料列受到影響)我现在有一字段值为:a,b,c 分隔符为逗号. 我现在想实现目地值为: 字段: id  name 1  a 2  b 3  c 在SQL中咋实现呢?declare @str varchar(8000) set @str = 'a1,b1,c2,d1,e3,f5' set @str =  'select  name='''+replace(@str,',',''''+' union all select ''')+'''' set @str='select id=identity(int,1,1),name into #temp from ([email protected]+') a select * from #temp drop table #temp'exec(@str)--参考:CREATE TABLE TB(ID VARCHAR(6), COLOR NVARCHAR(30))INSERT TBSELECT '173160',  N'#特深蓝色,#特深蓝色' UNION ALL SELECT '173160',  N'#特深蓝色,#特深蓝色' UNION ALL SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色'GOCREATE FUNCTION F_getStr(@color nvarchar(30))returns nvarchar(30)asbegin  declare @str nvarchar(30),@temp nvarchar(30)  set @str=''  set @temp=''  while charindex(',', @color+',')>0  begin    set @temp=left(@color, charindex(',', @color+',')-1)    if charindex(',[email protected]+',', ',[email protected]+',')=0      set @[email protected]+',[email protected]    set @color=stuff(@color, 1, charindex(',', @color+','), '')      end  return stuff(@str, 1, 1, '')endgoSELECT ID,dbo.F_getStr(COLOR) as COLOR FROM TBDROP TABLE TBDROP FUNCTION F_getStr/*ID     COLOR173160 #特深蓝色173160 #特深蓝色911169 #宝蓝色,#花灰色911169 #宝蓝色,#花灰色911169 #宝蓝色,#花灰色911169 #宝蓝色,#花灰色*/