0

I have a column that is returned by an SQL query.

enter image description here

Above is the column results, I need to separate the values in respect to a comma (,) to different columns.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
DENNIS KITHINJI
  • 217
  • 2
  • 14
  • 3
    This problem is already well covered on SO in other questions. Have you done any research into this before posting? – Tim Biegeleisen Jul 09 '18 at 05:40
  • Possible duplicate of [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) – EzLo Jul 09 '18 at 07:34

2 Answers2

0

Try This: 

SELECT s.PET_NAME
FROM Table1 CROSS APPLY
     STRING_SPLIT(PET_NAME, ',') s(PET_NAME);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Vipul
  • 896
  • 7
  • 14
  • 2
    Why was this upvoted? Don't people read the answer before they vote? – Zohar Peled Jul 09 '18 at 06:28
  • hi is there any issue. Please explain – Vipul Jul 09 '18 at 06:30
  • 1
    Several issues: 1. You are using the built in string_split function but it was only introduced in 2016 version. What if the OP is using an older version? You don't know because the question is missing the version tag. 2. You are using cross apply to the string split function but you are not selecting from it's result. 3. The OP requested the result in separate columns, the string_split can only do separate rows. – Zohar Peled Jul 09 '18 at 06:33
  • 1
    Am using 2016 sql server – DENNIS KITHINJI Jul 09 '18 at 06:48
  • @DENNISKITHINJI Well you should have tagged that. – Zohar Peled Jul 09 '18 at 06:54
0

For example via xml:

-- split to columns
select
    ltrim(rtrim(t.x.value('(x)[1]', 'varchar(20)'))) as pet1,
    ltrim(rtrim(t.x.value('(x)[2]', 'varchar(20)'))) as pet2,
    ltrim(rtrim(t.x.value('(x)[3]', 'varchar(20)'))) as pet3,
    ltrim(rtrim(t.x.value('(x)[4]', 'varchar(20)'))) as pet4,
    ltrim(rtrim(t.x.value('(x)[5]', 'varchar(20)'))) as pet5
from (
    select cast(('<x>'+replace(pet_name, ',','</x><x>')+'</x>') as xml) x
    from YourTable) t (x)

You can use for example substring function, but it's more complicated because non-deterministic count of possible columns. For ad-hoc parsing is the best tool text editor, for example excel (ribbon Data/Text to columns).

For spliting into columns you cannot use STRING_SPLIT (it will split into rows).

Deadsheep39
  • 561
  • 3
  • 16