0

I have data in a column which contains values for various fields from an application and all these fields are concatenated into one field on the database side and separated with commas. If the field in the application is blank, then the value between the two commas will just be blank.

I need a select statement to select each of the individual fields if they are populated. I would like to specify each field as a variable which I will declare at the top of the statement.

An example of the string in the database field is:

,"FIELD1","FIELD2","FIELD3",FIELD4,FIELD5,FIELD6,,,,"FIELD10",FIELD11,FIELD12,FIELD13

As you can see, fields 7-9 were blank in this example so they are blank in the string.

I just need a way to selectively select the field I need using the commas as my marker. The string always starts with a comma so field1 always comes after the first comma.

I hope this makes sense!

Chris
  • 7
  • 2
  • http://stackoverflow.com/questions/17481479/parse-comma-separated-string-to-make-in-list-of-strings-in-the-where-clause – Donal Aug 21 '14 at 12:00
  • Normalise your database schema to at least 1NF, and you won't have that problem in the first place! (That advice is a longer-term solution and probably won't be immediately useful to you.) – stakx - no longer contributing Aug 21 '14 at 12:58

1 Answers1

1

Try this:

DECLARE @STRING VARCHAR(255) = ',"FIELD1","FIELD2","FIELD3",FIELD4,FIELD5,FIELD6,,,,"FIELD10",FIELD11,FIELD12,FIELD13'

DECLARE @FieldToReturn INT = 12 -- Pick which field you want

SET @STRING = RIGHT(@STRING, LEN(@STRING) - 1) + ',' -- Strip leading comma & add comma to the end
WHILE @FieldToReturn > 1 
   BEGIN
     SET @STRING = SUBSTRING(@STRING,PATINDEX('%,%',@STRING), LEN(@STRING)) 
     SET @FieldToReturn = @FieldToReturn - 1
     SET @STRING = RIGHT(@STRING, LEN(@STRING) - 1)
   END

SELECT SUBSTRING(@STRING,0,PATINDEX('%,%', @STRING))

If it the field is not populated, this will return a blank.

Edit: I know that I could have put all of the string manipulation in one line within the WHILE, but chose not to for readability...to me that is more important that the possibility of a teeny tiny bit of overhead in this example

How 'bout a Fresca
  • 2,267
  • 1
  • 15
  • 26
  • That is perfect, it left out the last field because it does not have a comma following it, but when I set the @STRING variable I just concatenated a comma to the end of the field name and it works. Many thanks for this – Chris Aug 21 '14 at 12:31
  • No problem! Good catch on that! I edited my answer to just add a comma in the same line as stripping off the leading comma. – How 'bout a Fresca Aug 21 '14 at 12:55