-1

I have a table with a column that has multiple values stored in one column I want to query that data to a file and separate the values.

Example: This column (SHIFT_LENGTHS) has values stored as a concatenate of each day of the week with "#" as a separator which represents seconds for day of the week.

SELECT 
  NAME
 ,SHIFT_LENGTHS
FROM SHIFTGUARANTEE
ORDER BY NAME

SQL Results

NAME  ,SHIFT_LENGTHS
Shift1,#14400#14400#14400#14400#14400#14400#14400#

I want to query the data and present it in a file this way:

NAME  ,Mon  ,Tue  ,Wed  ,Thu  ,Fri  ,Sat  ,Sun
Shift1,14400,14400,14400,14400,14400,14400,14400
Iłya Bursov
  • 23,342
  • 4
  • 33
  • 57
Kenh426
  • 115
  • 1
  • 1
  • 6
  • I think this answer to a different question is something you could use - [How do I split a string so I can access item x?](https://stackoverflow.com/a/2677/352349). – Anssssss Mar 18 '16 at 16:03
  • Possible duplicate of [How do I split a string so I can access item x?](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) – Iłya Bursov Mar 18 '16 at 16:03
  • 1
    While i agree you need a string splitter, the one referenced above is the absolute worst possible one you can find from a performance perspective. Do NOT use a splitter that uses loops or recursive ctes. They are bad. Here are some far better options. http://sqlperformance.com/2012/07/t-sql-queries/split-strings – Sean Lange Mar 18 '16 at 16:13
  • 1
    The correct approach is of course to stop storing data like this and create either a table with 7 columns for the data, or store each value into a separate row. – James Z Mar 18 '16 at 16:20
  • If you really really want to split strings, Jeff Moden's [DelimitedSplit8k](http://www.sqlservercentral.com/articles/Tally+Table/72993/) is one of the best options – James Z Mar 18 '16 at 16:22

1 Answers1

1

If the format of the string is fixed as it seems to be you could just parse it using the substring function:

SELECT 
  NAME
 ,SUBSTRING(SHIFT_LENGTHS, 2, 5) mon
 ,SUBSTRING(SHIFT_LENGTHS, 8, 5) tue
 ,SUBSTRING(SHIFT_LENGTHS, 14, 5) wed
 ,SUBSTRING(SHIFT_LENGTHS, 20, 5) thu
 ,SUBSTRING(SHIFT_LENGTHS, 26, 5) fri
 ,SUBSTRING(SHIFT_LENGTHS, 32, 5) sat
 ,SUBSTRING(SHIFT_LENGTHS, 38, 5) sun
FROM SHIFTGUARANTEE
ORDER BY NAME

If the format isn't fixed you can use the charindex function to get the indexes of the separators, or a custom CLR regex function.

There's also a good solution using cross apply in the answers to another question: How to split a comma-separated value to columns

Community
  • 1
  • 1
jpw
  • 44,361
  • 6
  • 66
  • 86
  • Thank you very much, this works and what if the format of the string is not fixed? – Kenh426 Mar 18 '16 at 16:20
  • @Kenh426 If it's not fixed you should take a look at the answers in the question I linked to. It's a bit more complex, but not much. – jpw Mar 18 '16 at 16:21