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 last occurrences of a specific character inside a string. As an example, consider a string named "2010 - #### - 3434", and suppose the character you are looking for is "#". The first occurrence of the hash inside the line is at 6th position, and the last occurrence is at 9th position.

Answer 1

I don’t know how to do this, but regex functions like regexp_matches, regexp_replace, regexp_split_to_array can be an alternative way to solve your problem.

Answer 2

Well...

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

. , .

Answer 3

, char = '.', . , :

CREATE OR REPLACE FUNCTION last_post(text,char) 
RETURNS integer LANGUAGE SQL AS $$  
select length($1)- length(regexp_replace($1, E'.*\\' || $2,''));  
$$;

Answer 4

9.5+ array_positions

PostgreSQL, string_to_array(), array_positions() array_positions(string_to_array(str,null), c)

SELECT
  arrpos[array_lower(arrpos,1)] AS first,
  arrpos[array_upper(arrpos,1)] AS last
FROM ( VALUES
  ('2010-####-3434', '#')
) AS t(str,c)
CROSS JOIN LATERAL array_positions(string_to_array(str,null), c)
  AS arrpos;

Answer 5

SQL char , 1. 0, ... ( ) , (.$^()[]*+)

CREATE FUNCTION last_post(text,char) RETURNS integer AS $$ 
     select length($1)- length(regexp_replace($1, '.*' || $2,''));
$$ LANGUAGE SQL IMMUTABLE;

test=# select last_post('hi#-#-#byte','#');
 last_post
-----------
         7

test=# select last_post('hi#-#-#byte','a');
 last_post
-----------
         0

pl/pgSQL, rfusca.

Answer 6

Another way to calculate the last position is to cut the string into an array by delimon equal to the desired character, and then subtract the length of characters for the last item from the length of the whole string

CREATE FUNCTION last_pos(char, text) RETURNS INTEGER AS
$$
select length($2) - length(a.arr[array_length(a.arr,1)]) 
from (select string_to_array($2, $1) as arr) as a
$$ LANGUAGE SQL;

For the first position it’s easier to use

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