How To Select Specific Data Between Quotes (")

I am reposting my question as I am new to SQL 2012. I want to fetch the numeric data between quotes (') in the following rows, row1:'asdalknd,'1,2,3,4',slknsdl,'5,6,7,8',snlsn' ro

Solution 1:

this is Ugly, but will eventually work:

COLUMN = 'jksjdksls#$#$@@kskjfjf,"123,456,789" lsnslkdswfnslsjfls'

left( right(COLUMN,len(COLUMN)-instr(COLUMN,"""")), instr( right(COLUMN,len(COLUMN)-instr(COLUMN,"""")), """") -1 )

--> 123,456,789

This is what is done:

• We take this string 'jksjdksls#$#$@@kskjfjf,"123,456,789" lsnslkdswfnslsjfls'
• find the first occurence of " with instr(COLUMN,"""") --> returns 24
• take the right end of the string with. Therefore we need to take the length of the string with len(COLUMN)--> 55 and substract the position of the first " (24)
• then we need to find the second " with instr()in the right string, which we need to create again with right(COLUMN,len(COLUMN)-instr(COLUMN,"""")) and substract 1 for the ".