0

I have a requirement where I have a column in my SQL table in this format:

SQL Column

How to split the data using comma delimiter and insert it into newly added columns in the same table?

enter image description here

Improve this question
7
  • 5
    Something seems a bit off here. Why or how are you deciding which column the data ends up in? for this simplified example it is easy but I doubt your real data is so simple. Commented Feb 17, 2020 at 15:28
  • 1
    And to Sean's point, please edit your question and add some more representative data as text, not images, to help us help you. Commented Feb 17, 2020 at 15:32
  • This doesn't look like want want to split anything, and looks more like you want to have a column with a value if the other value has the value in it. If you split the data, then your final row would have a value 4 in col1 and NULL in the others (not 4 in col4 and NULL in the others). Commented Feb 17, 2020 at 15:36
  • Does this answer your question? How to split a comma-separated value to columns Commented Feb 17, 2020 at 15:38
  • 1
    I do hope this effort is to fix the design. Storing delimited data like this violates 1NF and is nothing short of horrific to work with. Commented Feb 17, 2020 at 15:55

2 Answers 2

Reset to default
3

Your sample data may not need any splitting. You want to move the data to a column based on the value it finds. You can do this a bit simpler than splitting the data. This works just fine for your sample data.

declare @Something table
(
    Combined_Column varchar(10)
)

insert @Something values
('1,2,3')
, ('2')
, ('1,3')
, ('1,2,3,4')
, ('1,3,4')
, ('1')
, ('4')

select *
    , col1 = case when charindex('1', s.Combined_Column) > 0 then 1 end
    , col2 = case when charindex('2', s.Combined_Column) > 0 then 2 end
    , col3 = case when charindex('3', s.Combined_Column) > 0 then 3 end
    , col4 = case when charindex('4', s.Combined_Column) > 0 then 4 end
from @Something s
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Beat me to it. :)
@Larnu that's a first. :D
@Sean Lange How about ('11,2,3') ? i.sstatic.net/4bt73.png
That isn't in the sample data. Only had values 1-4. And then in the comments they said they have 1-8. So how does 11 come into play? To be honest the question is so over simplified it isn't even funny. I provided a solution to the example provided.
Was going to suggest exactly this.
|
2

Demo on db<>fiddle

It seems to me you need to use CASE WHEN END to achieve it.

select value, case when CHARINDEX('1', value) > 0 then '1' else '' end col1,
       case when CHARINDEX('2', value) > 0 then '2' else '' end col2,
       case when CHARINDEX('3', value) > 0 then '3' else '' end col3,
       case when CHARINDEX('4', value) > 0 then '4' else '' end col4
from #a

Output

enter image description here

Updated Demo

In case of the value may be ('11,2,3'), You should use STRING_SPLIT like below to get the exact result.

select value, 
case when EXISTS(SELECT TOP 1 1 FROM STRING_SPLIT(value, ',') s where s.value = '1') then '1' else '' end col1,
case when EXISTS(SELECT TOP 1 1 FROM STRING_SPLIT(value, ',') s where s.value = '2') then '2' else '' end col2,
case when EXISTS(SELECT TOP 1 1 FROM STRING_SPLIT(value, ',') s where s.value = '3') then '3' else '' end col3,
case when EXISTS(SELECT TOP 1 1 FROM STRING_SPLIT(value, ',') s where s.value = '4') then '4' else '' end col4
from #a

enter image description here

Improve this answer

1 Comment

Does this answer your question? Let me know if you need any help @abhi8569

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.