特殊符号分隔的字符串序列分别列出的问题

Question

有个字段，多个字符串用特殊符号隔开，怎样把每个值放到一个字段里？用SQL 查询语句的话。

Answer 1

既然没例子，我也只能给个例子了：if OBJECT_ID('test') is not null
drop table test 
go 
create table test 
( 
    id int, 
    name varchar(10), 
    [key] varchar(20) 
) 
go 
insert test 
select 1,'lisa','li,is,sa' union all
select 2,'sophia','ab,cd,ef' union all
select 3,'lori','12,34,23'
go 
  
select
    id, 
    a.name, 
    SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key] 
from
    test a,master..spt_values 
where
    number >=1 and number<=len([key])  
    and type='p' 
    and substring(','+[key],number,1)=','
/* 
id    name    key
----------------------------- 
1    lisa    li 
1    lisa    is
1    lisa    sa 
2    sophia    ab 
2    sophia    cd 
2    sophia    ef 
3    lori    12 
3    lori    34 
3    lori    23 
*/ 

Answer 2

可以这样吗？ 
1    lisa    li     is      sa
2    sophia    ab  cd ef 
3    lori     12    34  23
抱歉没做数据，如果希望【key】那列的值可以分别放在列里，怎么做？

Answer 3

-- 供楼主参考，需要拼接sql语句的。。
 if OBJECT_ID('test') is not null
drop table test 
go 
create table test 
( 
    id INT PRIMARY KEY, 
    name varchar(10), 
    [key] varchar(50) 
) 
go 
insert test 
select 1,'lisa','li,is,sa' union all
select 2,'sophia','ab,cd,ef' union all
select 3,'lori','12,34,23,1,2,3,4,5,6'union all
 select 4,'4','1' 
go ----------------------------------DECLARE @maxct INT=0;SELECT @maxct=MAX(r.ct) FROM test t
CROSS APPLY(
SELECT COUNT(1) ct FROM dbo.[Split](t.[key],',') AS s
)r;DECLARE @sql NVARCHAR(MAX)='';
DECLARE @sqlC NVARCHAR(MAX)='';SET @sqlC=(SELECT ',MAX(CASE WHEN r.id='+ltrim(n.Rn)+' THEN r.Value END) [c'+ltrim(n.Rn)+']'
 FROM dbo.Nums AS n WHERE n.Rn<=9
 FOR XML PATH(''));SET @sql='
SELECT t.id,t.name
'+@sqlC+'
 FROM test t
CROSS APPLY(
SELECT * FROM dbo.[Split](t.[key],'','') AS s
)r
GROUP BY t.id,t.name
';
PRINT @sql
EXEC sp_executesql @sql;/*id          name       c1         c2         c3         c4         c5         c6         c7         c8         c9
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1           lisa       li         is         sa         NULL       NULL       NULL       NULL       NULL       NULL
2           sophia     ab         cd         ef         NULL       NULL       NULL       NULL       NULL       NULL
3           lori       12         34         23         1          2          3          4          5          6
4           4          1          NULL       NULL       NULL       NULL       NULL       NULL       NULL       NULL
警告: 聚合或其他 SET 操作消除了 Null 值。*/--  dbo.Split方法和 dbo.Nums表见-- http://blog.csdn.net/feiazifeiazi/article/details/17242355

Answer 4

id name
1 a,b,c
2 x,y,z
期待结果
id name1 name2 name3 
1 a             b            c
2 x              y             z 
name列里肯定由2个逗号分隔，所以肯定可以再分成3列，就是不知怎么写好？

Answer 5

只有三个就好办：----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-18 16:39:42
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
-- Apr  2 2010 15:48:46 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([id] int,[name] nvarchar(10))
insert [huang]
select 1,'a,b,c' union all
select 2,'x,y,z'
--------------生成数据--------------------------select id,PARSENAME(REPLACE(name,',','.'),3)NAME1,PARSENAME(REPLACE(name,',','.'),2) NAME2,PARSENAME(REPLACE(name,',','.'),1)name3
from [huang]
----------------结果----------------------------
/* 
id          NAME1                                                                                                                            NAME2                                                                                                                            name3
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
1           a                                                                                                                                b                                                                                                                                c
2           x                                                                                                                                y                                                                                                                                z*/

Answer 6

到我这里就不好用了，我的sql server是Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1，我的电脑是32位xp系统。是这里有问题吗？parsename()再研究研究。

Answer 7

表名改了，其它的都没变，可是结果不像预期的那样。不过没使用过parsename，倒是可以用用看。谢谢了！

Answer 8

2008R2没有直接的函数对数组进行处理，需要自定义“标量值函数”(
 @str nvarchar(MAX),  --要分割的字符串
 @split nvarchar(MAX)  --分隔符号
)
returns nvarchar(MAX)
as
 begin
  declare @location int
  declare @start int
  declare @length int
  set @str=ltrim(rtrim(@str))
  set @location=charindex(@split,@str)
  set @length=1
   while @location<>0
     begin
      set @start=@location+1
      set @location=charindex(@split,@str,@start)
      set @length=@length+1
     end
   return @length
 end
(
 @str nvarchar(max),  --要分割的字符串
 @split nvarchar(10),  --分隔符号
 @index int --取第几个元素
)
returns nvarchar(max)
as
begin
 declare @location int
 declare @start int
 declare @next int
 declare @seed int
 set @str=ltrim(rtrim(@str))
 set @start=1
 set @next=1
 set @seed=len(@split)
 set @location=charindex(@split,@str)
 while @location<>0 and @index>@next
   begin
    set @start=@location+@seed
    set @location=charindex(@split,@str,@start)
    set @next=@next+1
   end
 if @location =0 select @location =len(@str)+1 --这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
 return substring(@str,@start,@location-@start)
end看不懂的话就搜索一下吧~

Answer 9

create table test_a
(
code VARCHAR(10),
re VARCHAR(10)
)
insert into test_a
select 'A','1' UNION
select 'A','2' UNION
select 'A','3' UNION
select 'B','11' UNION
select 'B','22' UNION
select 'C','33' 
--code    re
--A    1,2,3
--B    11,22
--C    33
--执行查询
 select code ,stuff((SELECT ',' + b.re FROM test_a AS b WHERE b.code = a.code FOR XML PATH('')) ,1,1,'') FROM test_a AS a GROUP BY code
 --执行结果
 ---------- -----------------
A          1,2,3
B          11,22
C          33
 
(3 行受影响)
--再将合并后的拆开
--创建函数
create   function   f_splitstr(@SourceSql   varchar(8000),@StrSeprate   varchar(100))   
  returns   @temp   table(F1   varchar(100))   
  as     
  begin   
  declare   @ch   as   varchar(100)   
  set   @SourceSql=@SourceSql+@StrSeprate     
  while(@SourceSql<>'')   
                  begin   
                  set   @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1)   
  insert   @temp   values(@ch)   
  set   @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')   
                  end   
  return   
  end  
 --借助上一个查询的结果 
;WITH acte AS (
 select code ,stuff((SELECT ',' + b.re FROM test_a AS b WHERE b.code = a.code FOR XML PATH('')) ,1,1,'') AS re FROM test_a AS a GROUP BY code)
 --执行
 SELECT b.f1 , acte.* FROM acte cross apply f_splitstr(acte.re , ',')  AS b 
 --执行结果
f1     code       re
------ ---------- -------------
1       A          1,2,3
2       A          1,2,3
3       A          1,2,3
11      B          11,22
22      B          11,22
33      C          33
 
(6 行受影响)

Answer 10

自己做测试数据，也好用。使用数据表里的数据，就不能用了。
'张三|    女|      18401809981| [email protected]'
使用的是“|”分隔。

Answer 11

[email protected] 这里的.号影响了