-1

In SQL Server, I would like to split a string field from a table, my data is as below:

For example:

Select field1 
from table1;

Output:

s1,t1 1,a 2,b 3,c 4,d

After splitting this string, I would like the output to be a table with 2 columns:

s1 t1
-----
1  a
2  b
3  c
4  d

I just realized, I can work with this kind of output as well, if any leads please share.

s1,t1

1,a
2,b
3,c
4,d
user9706045
  • 1
  • 1
  • I didn't realize there was a table until the post was edited. – DxTx Apr 26 '18 at 17:59
  • There are several "split string" functions you can find on StackOverflow, and SQL2016 even includes a function to split strings. One of them will do exactly what you want. – pmbAustin Apr 26 '18 at 18:16
  • 2
    The best solution would be to stop storing delimited data. It violates 1NF. But here are several great choices for splitters. http://sqlperformance.com/2012/07/t-sql-queries/split-strings – Sean Lange Apr 26 '18 at 18:17
  • 1
    Ugh...just realized that your column names are the first delimited value. What a complete nightmare to work with. You are going to need dynamic sql on top of splitting these values. And from the looks of it you are going to have to split them multiple times. Given that I would look at this splitter. http://www.sqlservercentral.com/articles/Tally+Table/72993/ It will be needed to have the ordinal position returned so you can match up rows to the originals. YUCK!!! – Sean Lange Apr 26 '18 at 18:19
  • Are the number of columns also dynamic? Or do you always have exactly two columns, but the names are in the database? – Sean Lange Apr 26 '18 at 18:29
  • 1
    Was that data imported from a CSV? If so, there are far easier ways of importing and processing the data. – UnhandledExcepSean Apr 26 '18 at 18:35
  • No, I can work with 1 column as well, the number of values in the string is changing occassionally. – user9706045 Apr 26 '18 at 18:41
  • Possible duplicate of [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) – Tab Alleman Apr 26 '18 at 18:42
  • This answer has a double-split-string (split on two delimiters... in your case, a comma and a space): https://stackoverflow.com/questions/28772227/tsql-nested-split-parse-of-string-into-table-multiple-concatenated-tagvalue-i/28773089#28773089 So I would start here. – pmbAustin Apr 26 '18 at 19:02
  • @pmbAustin loops splitters like that are just horribly inefficient. – Sean Lange Apr 26 '18 at 19:06
  • He's worried about functionality, not efficiency... it's most important to get something that works, and that is a starting point. Once they get something working correctly, they can see if it's efficient enough for their needs. No use in premature optimization when nothing is working yet. – pmbAustin Apr 26 '18 at 19:09
  • I would disagree here. Using a loop when one isn't needed is kind of silly. There are so many great options for splitters that are so much faster it isn't a comparison. This isn't premature optimization at all. That is defined as doing something that changes the design or a strange method to solve a performance problem that doesn't exist. Using a set based splitter instead of a loop does not fall into that category. – Sean Lange Apr 26 '18 at 19:11

2 Answers2

1

Here is a way to get the delimited sets of values using Jeff Moden's DelimitedSplit8K. It is super fast. http://www.sqlservercentral.com/articles/Tally+Table/72993/

Notice I am using table variable here. You would want to use your actual table.

declare @table1 table
(
    field1 varchar(1000)
)

insert @table1
select 's1,t1 1,a 2,b 3,c 4,d'
;

select max(case when ColumnValues.ItemNumber = 1 then ColumnValues.Item end)
    , max(case when ColumnValues.ItemNumber = 2 then ColumnValues.Item end)
from @table1 t
cross apply dbo.DelimitedSplit8K(t.field1, ' ') GroupedValues
cross apply dbo.DelimitedSplit8K(GroupedValues.Item, ',') ColumnValues
group by GroupedValues.ItemNumber

This will return:

s1  t1
1   a
2   b
3   c
4   d
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • since [this question is old](https://stackoverflow.com/questions/50032887/create-table-6-x-6-with-automatic-spill-from-upline) I figured it may not get much attention. I'm interested if you can think of something better or understand what they are trying to do more. I'll delete this comment tomorrow – S3S Apr 26 '18 at 21:26
  • Thanks a lot!! This solution works great. You saved my day. – user9706045 Apr 26 '18 at 23:19
  • @user9706045 don’t forget to accept this answer then – S3S Apr 26 '18 at 23:36
0

You can use STRING_SPLIT to get the desired output...

    SELECT DISTINCT 
            LEFT(s.value,CHARINDEX(',')-1 AS s1,
            SUBSTRING(s.value,CHARINDEX(',')+1,LEN(s.value)) AS t1
    FROM table1 AS t
    CROSS APPLY STRING_SPLIT(t.field1, ' ') AD s
jigga
  • 558
  • 4
  • 16