VoidKing VoidKing - 3 months ago 7
SQL Question

How can I substring "val2" from a string formatted like "val1A-val2A~val1B-val2B~val1C-val2C", etc, based on the condition of matching it with "val1"?

In a certain SQL table I'm working on, there's a column that contains data formatted like:

"year-text~year-text~year-text~year-text~year-text~year-text~year-text~" and so on and so forth.

(year is in 'yyyy' format)

(for example):
"2012-10000~2013-5000~2014-500~2015-50000~2016-100~"

How, using SQL might I extract, say, the value "50000" based on having the year, "2015"

Things to note/clarify:


  • The "-" and "~" characters can be trusted as delimiters. That is, they do not exist within any of the values or, of course, the years.

  • No year exists without a value. In other words, if the value becomes blank, the year is stripped out, as well (In other words, the stored string will never have an "-" and a "~" right next to each other, such as 2016 in the string "2015-200~2016-~2014-1000", for example).

  • The years in the string may not be in chronological order from left to right.

  • There could be virtually any number of years (each with a value) in the string or, indeed, none, at all. If no year/value pair exists for the column, the value becomes NULL

  • Please note that after each value for each year the character "~" is applied even if it is the last year/value pair. Any string value that is not NULL will therefore always end with a "~".


Answer

Here is an option which uses SUBSTRING and CHARINDEX to get the job done:

SUBSTRING(col,
          CHARINDEX('2015', col) + 5,
          CHARINDEX('~', col, CHARINDEX('2015', col) + 5) - (CHARINDEX('2015', col) + 5))

In the sample input

2012-10000~2013-5000~2014-500~2015-50000~2016-100~

CHARINDEX('2015', col) + 5 would start at the 5 in the number 50000 after the occurrence of 2015.

The term

CHARINDEX('~', col, CHARINDEX('2015', col) + 5) - (CHARINDEX('2015', col) + 5)

yields the length of the number 50000, which in this case would be 5.

Comments