How to find the first and last occurrences of a specific character inside a string in PostgreSQL

Question

I want to find the first and the last occurrences of a specific character inside a string. As an example, consider a string named "2010-####-3434", and suppose the character to be searched for is "#". The first occurrence of hash inside the string is at 6-th position, and the last occurrence is at 9-th position.

Answer 1

Well...

Select position('#' in '2010-####-3434');

will give you the first. If you want the last, just run that again with the reverse of your string. A pl/pgsql string reverse can be found here.

Select length('2010-####-3434') - position('#' in reverse_string('2010-####-3434')) + 1;

Answer 2

My example:

reverse(substr(reverse(newvalue),0,strpos(reverse(newvalue),',')))

1. Reverse all string
2. Substring string
3. Reverse result