SQL Parse and retrieve value

Question

I have recently had a database table change which has grouped three table values in one column. The new column holds the 3 values and delimits them with ~

(Eg. Col Name: OL_From Value: 2~7548~186)

My old query was using the second value as an index for a left join but now I need to parse out the value and serve it up for as a value for the left join.

The old query looked like

left join run r2 on sr.from_run_id = r2.run_id

However now the "from_run_id" field no longer exists as it is the second value of the above example (Eg. 2~7548~186)

splitting the string up with string_split seems easy enough but how do I then retrieve my second value and serve it back to the join.

Thanks for your time.

Ha ha tell me about it, I protested this but unfortunately nothing can be done about it. — Sam, Apr 04 '18 at 18:18
well you can show them this question https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Juan Carlos Oropeza, Apr 04 '18 at 18:35
Thanks Juan, I'm using sql server. The solution I came up with was (SELECT PARSENAME(REPLACE(sr.OL_To, '~', '.'), 1)) — Sam, Apr 06 '18 at 14:28

score 0 · Answer 1 · answered Apr 04 '18 at 18:48

just create a function to encapsulate the parsing and do

 ON getIdFromField(sr.from_run_id) = r2.run_id

For example in postgresql you can use regexp_split_to_array

SQL DEMO

WITH start_string as (
    SELECT '2~7548~186'::text as value

)   
select a[1], a[2], a[3], a[4]
from (
    select regexp_split_to_array(value, '~')
    FROM start_string
) as dt(a)

and return a[2].

OUTPUT

SQL Parse and retrieve value

1 Answers1