0

I have the following data:

ID | First | Last | Comments

456| John | Smith | 1. alpha 2. bravo 3. charlie

132| Tom | Ryan | 1. zulu 2. yankee 3. xray

I am trying to format it as such:

ID | First | Last | Comments

456| John | Smith | 1. alpha

456| John | Smith | 2. bravo

456| John | Smith | 3. charlie

132| Tom | Ryan | 1. zulu

132| Tom | Ryan | 2. yankee

132| Tom | Ryan | 3. xray

Because each comment is a different length I cannot use a function like LEFT(). The comments are always preceded by the number and a period however there are 30 comments per person. This lead me to CHARINDEX() and it seemed I could build a CASE statement with it, however because the data type for the Comments column is Text, CHARINDEX() won't work. Is there a workaround to separate this column?

S3S
  • 24,809
  • 5
  • 26
  • 45
kjh23
  • 51
  • 2
  • 7
  • There are workarounds, but a true **fix** for this would be to normalize your data. Storing delimited strings in a column, as you have discovered, is a real pain to work with. Any chance you can change that? – S3S Jan 28 '19 at 19:00
  • You might be able to use STRING_SPLIT - which would really make your life easier. Otherwise, you will want to implement a function. There are several approaches - tally table, xml, and recursive cte are the methods I can think of off the top of my head. https://stackoverflow.com/questions/46902892/string-split-in-sql-server-2012 – Ryan B. Jan 28 '19 at 19:11
  • @RyanB. not in 2012 unfortunately but [this is a good alternative](http://www.sqlservercentral.com/articles/Tally+Table/72993/). – S3S Jan 28 '19 at 19:13
  • @scsimon Unfortunately the database is the back-end of an application. I couldn't say why anyone used Text! – kjh23 Jan 28 '19 at 19:59

0 Answers0