3

Here is my issue:

I have a column with the following data in an sql column:

Answers
=======
1:2:5: <--- notice my delimiter

I need to be able to break up the digits into a result set that i can join against a lookup table such as

Answers_Expanded
=======
1 apple
2 pear
3 cherry
4 mango
5 grape

and return

Answers
=======
apple pear grape

Any such way?

Thanks!

Pinch
  • 4,009
  • 8
  • 40
  • 60
  • Maybe PIVOT ? have a look at this article, it will probably help you http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx – Scotch Feb 26 '13 at 20:05
  • 1
    You Can do like this http://stackoverflow.com/questions/14911167/split-function-in-sql-server-2008/14918846#14918846 – Mikael Eriksson Feb 26 '13 at 20:17

3 Answers3

1

This blog post has a good example of a user defined function that will return a table with the values from your delimited string in a column. You can then join that table to your Answers_Expanded table to get your value.

This works fine if you are parsing reasonably short strings, and if you are doing it as a one time thing, but if you have a table with your answers stored in a column like that, you don't want to be running this on the whole table as it will be a large performance hit. Ideally you'd want to avoid getting delimited strings like this in SQL.

Jeremy Hutchinson
  • 1,975
  • 16
  • 26
1

This is a bit of a hack (the LIKE, the XML PATH, and the STUFF), and it assumes that you want the answers ordered by their ID as opposed to matching up with the original order in the multivalued column...

But this gives the results you're looking for:

SELECT STUFF((
  SELECT ' ' + ae.Answer
  FROM
    Answers_Expanded ae
    JOIN Answers a ON ':' + a.Answers LIKE '%:' + CAST(ae.ID AS VARCHAR) + ':%' 
  ORDER BY ae.ID
  FOR XML PATH(''))
, 1, 1, '') AS Answers

Sql Fiddle

This works because:

  1. Joining with LIKE finds any Answer_Expanded rows that match the multivalued column.
  2. XML PATH simulates a group concatenation... and allows for ' ' to be specified as the delimiter
  3. STUFF removes the leading delimiter.
Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
0

i would suggest that you save your answers in a way that one cell has only one number...not multible information in one cell. (violation of the 1st normal form).

otherwise you better use some higher sql language such as T-SQL.

RAN
  • 508
  • 6
  • 18