Split one columnn data by comma into temp table (SQL 2014)

Transact-SQL
1

hi,

i have one column in varchar.
Example data: GOLDEN BLOSSOM,UAE,13 or GOLDEN BLOSSOM, UAE , 13

two sample data got space and no space before or after comma.
would like to split it into 3 columns into one temp table

i am using MSQL 2014

i would like to split it into 3 columns. if there is more comma, ignore it and include it to last columns.

3

sorry, below are my requirement.Capture

4

thanks, i want to split to 3 columns instead of 3 rows

6 
IF OBJECT_ID('tempdb.dbo.#data') IS NOT NULL DROP TABLE #data;
CREATE TABLE #data ( data varchar(8000) NULL ); 
INSERT INTO #data VALUES('GOLDEN BLOSSOM,UAE,13'), ('GOLDEN BLOSSOM, UAE , 13,har,pra')
/*this code also handles fewer than 3 commas, just in case*/
INSERT INTO #data VALUES('GOLDEN BLOSSOM,UAE'), ('GOLDEN BLOSSOM NOTHING ELSE')

SELECT 
    data,
    LTRIM(RTRIM(CASE WHEN first_comma = 0 THEN data ELSE LEFT(data, first_comma - 1) END)) AS name,
    LTRIM(RTRIM(CASE WHEN second_comma = 0 THEN SUBSTRING(data, first_comma + 1, 8000)
        ELSE SUBSTRING(data, first_comma + 1, second_comma - first_comma - 1) END)) AS Country,
    LTRIM(RTRIM(CASE WHEN second_comma = 0 THEN '' ELSE SUBSTRING(data, 
        second_comma + 1, CASE WHEN third_comma = 0 THEN 8000 
        ELSE third_comma - second_comma - 1 END) END)) AS Pax
FROM #data
CROSS APPLY (
    SELECT CHARINDEX(',', data) AS first_comma,
        CHARINDEX(',', data, CHARINDEX(',', data) + 1) AS second_comma
) AS ca1
CROSS APPLY (
    SELECT CHARINDEX(',', data, second_comma + 1) AS third_comma
) AS ca2
7

hi

i tried to do this

please see if its what you want !!!!

please click arrow to the left for drop create data script 
drop table #sampledata
go 

create table #sampledata 
(
[column] varchar(100)
)
go 

insert into #sampledata select 'GOLDEN BLOSSOM,UAE,13'
insert into #sampledata select 'GOLDEN BLOSSOM, UAE , 13,har,pra'
go 

select 'sample data ',*from #sampledata
go

image
image966×226 4.12 KB

please click arrow to the left for SQL Script 
select 'SQL Output',
LEFT([column],CHARINDEX(',', [column])-1) first ,
LEFT(substring([column],LEN(LEFT([column],CHARINDEX(',', [column])-1))+1+1,LEN([column])) ,CHARINDEX(',', substring([column],LEN(LEFT([column],CHARINDEX(',', [column])-1))+1+1,LEN([column])))-1) as second ,
substring([column],LEN(LEFT([column],CHARINDEX(',', [column])-1)) + LEN(LEFT(substring([column],LEN(LEFT([column],CHARINDEX(',', [column])-1))+1+1,LEN([column])) , CHARINDEX(',', substring([column],LEN(LEFT([column],CHARINDEX(',', [column])-1))+1+1,LEN([column])))-1))+2,100) as third 
from 
#sampledata

image
image1071×216 4.82 KB

8

Here is a slightly different approach:

 Select dd.[data]
      , name    = ltrim(rtrim(substring(data, 1, coalesce(nullif(a.comma, 0), len(data) + 1) - 1)))
      , Country = iif(a.comma <> 0, ltrim(rtrim(substring(data, a.comma + 1, coalesce(nullif(b.comma, 0), len(data) + 1) - a.comma - 1))), '')
      , Pax     = iif(b.comma <> 0, ltrim(rtrim(substring(data, b.comma + 1, coalesce(nullif(c.comma, 0), len(data) + 1) - b.comma - 1))), '')
      , Col4    = iif(c.comma <> 0, ltrim(rtrim(substring(data, c.comma + 1, coalesce(nullif(d.comma, 0), len(data) + 1) - c.comma - 1))), '')
   From #data                                                           As dd
  Cross Apply (Values (charindex(',', data, 1)))                        As a(comma)
  Cross Apply (Values (charindex(',', data, nullif(a.comma, 0) + 1)))   As b(comma)
  Cross Apply (Values (charindex(',', data, nullif(b.comma, 0) + 1)))   As c(comma)
  Cross Apply (Values (charindex(',', data, nullif(c.comma, 0) + 1)))   As d(comma);

I included Col4 just for display purposes - and added to additional rows to the test table:

 Insert Into #data
 Values ('GOLDEN BLOSSOM,UAE,13')
      , ('GOLDEN BLOSSOM, UAE , 13,har,pra')
      , ('NO COUNTRY OR PAX')
      , ('COUNTRY NO PAX,US');
9

Have a look at this solution.