From a list of multiple possible substring values, get the lowest index

Database

From a list of multiple possible substring values, get the lowest index

I'm selecting a column of strings (which, for clarity I will refer to as 'titles'), and have a ready list of values that can be possible substrings. With only one value, that's not an issue. For example:

select case when POSITION('is' IN 'this is a test') = 0 then 'this is a test'
else SUBSTRING('this is a test' FROM 1 FOR POSITION('is' IN 'this is a test') -1) end

'this is a test' represents the title and 'is' represents the value I'm looking for.

For the single value 'is', I search the titles, and if 'is' is found, I select the titles up to the index of the value. So the result of the query above will be 'th'.

However, what if I have 4 possible values, and I want to find the one (if exists) that appears at the lowest index (=appears "the earliest") in the title, and get the substring up to its position - I couldn't figure out a way to do this. Would appreciate any ideas.

1 REPLY

Re: From a list of multiple possible substring values, get the lowest index

Found what I was looking for...

regexp_replace('this is a test', '(\W)is(.*)|(\W)a t(.*)', '', 1, 1 ,'i')