CODEWITHSUNDEEP
sqlpostgresql
avi
avi

Reputation: 1846

Find sub string position from the end of string in PostgreSQL

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?

Upvotes: 3

Views: 10739

Answers (2)

Masoud
Masoud

Reputation: 1

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
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

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')))

Upvotes: 5

Related Questions