CODEWITHSUNDEEP
sqlsql-serversql-server-2008substring
rohit patil
rohit patil

Reputation: 159

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

Upvotes: 2

Views: 2056

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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.

Upvotes: 2

DhruvJoshi
DhruvJoshi

Reputation: 17146

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

Upvotes: 1

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1726

;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

Upvotes: 0

Related Questions