SQL String Spilt by Comma and spaces

I want to spilt data column into 3 new columns

  1. last
  2. first
  3. post

data have values with Spaces OR ',' Comma Se-prated.

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

Required Output :

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

check first row data column have 2 commas and last row have only one comma.

sample created here http://rextester.com/BEHUP42399

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

It tried for 'last' name Column but giving wrong o/p for 'KA E SYME' i.e. Single comma record. if data have single comma and before comma there is only one word means after comma there are two words then result will be. Check Last Record ABVC.

eg. ABC, PQR DIR                

3 answers

  • answered 2017-10-11 10:24 Yogesh Sharma

    You could try it using 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;
    

    Desired Result :

    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
    

    Note : The above result as per data provided.

  • answered 2017-10-11 10:24 DhruvJoshi

    you can try a query like below

    Logic is that we find the number of commas and add commas for first occurring spaces when commas are less than 3 This also works when there are no commas at all in string.

    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

  • answered 2017-10-11 10:24 Alfaiz Ahmed

    ;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