I have this query which finds the position of sub string.
select position('-' || lower('i') in lower('GFT-iMB5-i'))
(this is an example it actually uses function variables to replace the strings)
This return 4
In a nut shell I want it to begin from the end of the string and not from the start.
meaning I want to get: 9
How can I do that?
One method is to reverse the values and do comparison that way:
select length('GFT-iMB5-i') - position(reverse('-' || lower('i')) in reverse(lower('GFT-iMB5-i')))
position returns 0 when the searched substring not found. That could be emulated with f.ex. length(...) - coalesce(nullif(position(...), 0), length(...)).The post by @Gordon Linoff assumes that the last '-' searched for follows the char 'i'. But if this is generic need, the below might be helpful.
However the reverse function suggested by Gordon helps
with TMP as
(select 1 id, 'GFT-iKJ5-c' col1
union
select 2 id, 'GJT-iMB5-bdi' col1
union
select 3 id, 'NDT-iMJ7-ipt' col1
union
select 4 id, 'MDF-iDF-il' col1)
select id, col1, reverse(substring(trim(reverse(col1)), 1, position('-' in trim(reverse(col1))) - 1)) last_str
from TMP
;
