SQL字符串由逗号溢出和空间

Question

我想泼data列到3分新列

1. 最后
2. 第一
3. 后

数据有空格或值 ''逗号分开。

data 
ABC, RICK SEAN, MD 
MES, AEL B, MD FACC 
DAN, RK, MD 
OHI, NIK, MD F 
KA E SYME, PA-C 
ALL MUD, SIM, MD 
RINE EEMAN, FNP-C 
ABC, PQR DIR 

所需的输出：

desc      last  first  post 

ABC, RICK SEAN, MD   ABC   RICK SEAN MD   
MES, AEL B, MD FACC   MES   AEL B  MD FACC 
DAN, RK, MD     DAN   RK   MD 
OHI, NIK, MD F    OHI   NIK   MD F 
KA E SYME, PA-C    KA   E SYME  PA-C 
ALL MUD, SIM, MD   ALL MUD  SIM   MD 
RINE EEMAN, FNP-C   RINE  EEMAN  FNP-C 
ABC, PQR DIR    ABC   PQR   DIR 

检查第一行数据列有2个逗号和最后一排只有一个逗号。

样品这里http://rextester.com/BEHUP42399

select left(data, charindex(',', data) -1) from TTT 

它尝试了“最后”的名字列，但给人错误o对于“KAè赛姆”即一个逗号记录/ P创建。 如果数据有单个逗号，并且在逗号之前只有一个单词表示逗号后有两个单词，那么结果就是。检查最后一个记录ABVC。

eg. ABC, PQR DIR     

Answer 1

你可以尝试使用它CROSS APLLY：

SELECT N.DATA [descr], 
     CASE 
      WHEN N.last = '' 
      THEN LTRIM(N.first) 
      ELSE LTRIM(N.last) 
     END last, 
     CASE 
      WHEN N.last = '' 
      THEN SUBSTRING(LTRIM(N.post), 1, CHARINDEX(CHAR(32), LTRIM(N.post))) 
      ELSE LTRIM(N.first) 
     END first, 
     CASE 
      WHEN N.last = '' 
      THEN SUBSTRING(LTRIM(N.post), (CHARINDEX(' ', LTRIM(N.post))+1), LEN(LTRIM(N.post))) 
      ELSE LTRIM(N.post) 
     END post 
FROM 
(
    SELECT D.DATA, 
      CASE 
       WHEN D.post IS NULL 
       THEN LEFT(D.last, CHARINDEX(' ', D.last)) 
       ELSE D.last 
      END last, 
      CASE 
       WHEN d.post IS NULL 
       THEN SUBSTRING(D.last, (CHARINDEX(' ', D.last)+1), LEN(D.last)) 
       ELSE d.first 
      END first, 
      CASE 
       WHEN D.post IS NULL 
       THEN d.first 
       ELSE d.post 
      END post 
    FROM 
    (
     SELECT DISTINCT 
       DATA, 
       Split.a.value('/M[1]', 'NVARCHAR(MAX)') last, 
       Split.a.value('/M[2]', 'NVARCHAR(MAX)') first, 
       Split.a.value('/M[3]', 'NVARCHAR(MAX)') post 
     FROM 
     (
      SELECT DATA, 
        CAST('<M>'+REPLACE(DATA, ',', '</M><M>')+'</M>' AS XML) AS String 
      FROM #TM 
     ) A 
     CROSS APPLY String.nodes('/M') Split(a) 
    ) D 
) N; 

所需的结果：

desc      last  first  post 

ABC, RICK SEAN, MD   ABC   RICK SEAN MD   
MES, AEL B, MD FACC   MES   AEL B  MD FACC 
DAN, RK, MD     DAN   RK   MD 
OHI, NIK, MD F    OHI   NIK   MD F 
KA E SYME, PA-C    KA   E SYME  PA-C 
ALL MUD, SIM, MD   ALL MUD  SIM   MD 
RINE EEMAN, FNP-C   RINE  EEMAN  FNP-C 
ABC, PQR DIR    ABC   PQR   DIR 

注：以上结果按提供的数据。

Answer 2

你可以尝试像下面

逻辑查询的是，我们发现逗号的数量，并添加逗号的第一次出现空格时逗号小于3 这时候有没有逗号的所有字符串也适用。

create table staging_tbl_single_row (data varchar(max)) 
insert into staging_tbl_single_row values 
('ABC, RICK SEAN, MD') 
,('MES, AEL B, MD FACC') 
,('DAN, RK, MD') 
,('OHI, NIK, MD F') 
,('KA E SYME, PA-C') 
,('ALL MUD, SIM, MD') 
,('RINE EEMAN, FNP-C'), 
('ABC, PQR DIR'); 

; with cte as 
(
select 
    row_number() over (order by (select NULL)) as column1, 
    column2= 
    case 
     when 
      (len(data)-len(replace(data,',',''))=2) 
     then 
      data 
     when 
      (len(data)-len(replace(data,',',''))=1) 
     then 
      case --check if space is to the left of comma 
        when 
         charindex(' ',left(data, charindex(',',data)))<>0 
        then 
         stuff(data,charindex(' ',data),1,',') 
        else 
         left(data, charindex(',',data))+ 
         stuff(
          substring(data, 
             charindex(',',data)+1, 
             len(data) 
            ), 
          charindex(' ', 
             substring(
              data, 
              charindex(',',data)+1, 
              len(data) 
             ), 
             2) 
          ,1,',') 
        end 

     when 
      (len(data)-len(replace(data,',',''))=0) 
     then 
       stuff(
     stuff(data,charindex(' ',data),1,',') 
     ,charindex(' ',stuff(data,charindex(' ',data),1,',')) 
     ,1,',') 

    end,data 
    from 
    staging_tbl_single_row 
) 

select 
    last=[1],first=[2],post=[3] 
from 
(
    select 
     t.column1, 
     split_values=SUBSTRING(t.column2, t1.N, ISNULL(NULLIF(CHARINDEX(',',t.column2,t1.N),0)-t1.N,8000)), 
     r= row_number() over(partition by column1 order by t1.N) 
    from cte t 
     join 
     (
      select 
       t.column2, 
       1 as N 
      from cte t 
       UNION ALL 
      select 
       t.column2, 
       t1.N + 1 as N 
      from cte t 
       join 
       (
       select 
        top 8000 
         row_number() over(order by (select NULL)) as N 
       from 
        sys.objects s1 
         cross join 
        sys.objects s2 
       ) t1 
      on SUBSTRING(t.column2,t1.N,1) = ',' 
     ) t1 
      on t1.column2=t.column2 
)a 
pivot 
( 
    max(split_values) for r in ([1],[2],[3]) 
    )p 

see working demo

Answer 3

;WITH Split_Names (data, xmlname) 
AS 
(
    SELECT data, 

    CONVERT(XML,'<Names><name>' 
    + REPLACE(data,',', '</name><name>') + '</name></Names>') AS xmlname 
     FROM TTT 
) 

SELECT CASE 
      WHEN s.post IS NULL 
      THEN LEFT(s.last, CHARINDEX(' ', s.last)-1) 
      ELSE s.last 
     END last, 
     CASE 
      WHEN s.post IS NULL 
      THEN SUBSTRING(s.last, (CHARINDEX(' ', s.last)+1), LEN(s.last)) 
      ELSE s.first 
     END first, 
     CASE 
      WHEN s.post IS NULL 
      THEN s.first 
      ELSE s.post 
     END Post FROM 
(
SELECT data,  
xmlname.value('/Names[1]/name[1]','varchar(MAX)') AS Last,  
xmlname.value('/Names[1]/name[2]','varchar(MAX)') AS First, 
xmlname.value('/Names[1]/name[3]','varchar(MAX)') AS Post 
FROM Split_Names 
)s