CODEWITHSUNDEEP
postgresqlplpgsql
MD Sayem Ahmed
MD Sayem Ahmed

Reputation: 29186

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

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.

Upvotes: 16

Views: 64499

Answers (8)

Rafs
Rafs

Reputation: 814

The first occurrence can be found using position and the last occurrence can be found using position, length and reverse:

SELECT position('#' IN '2010##343##4'); => 6

Note: If the character doesn't exist in the string, the first occurrence position will be 0 here.

SELECT 1 + length('2010##343##4') - position('#' IN reverse('2010##343##4')); => 11

Note: If the character doesn't exist in the string, the last occurrence position will be length(string) + 1 here.

I changed the example to be more interesting. Tested on Postgres 16.

Upvotes: 0

Peter Tillemans
Peter Tillemans

Reputation: 35351

I do not know how to do that, but the regular expression functions like regexp_matches, regexp_replace, and regexp_split_to_array may be an alternative route to solving your problem.

Upvotes: 3

Tomasz Kozik
Tomasz Kozik

Reputation: 101

My example:

reverse(substr(reverse(newvalue),0,strpos(reverse(newvalue),',')))
  1. Reverse all string
  2. Substring string
  3. Reverse result

Upvotes: 9

Evan Carroll
Evan Carroll

Reputation: 1

9.5+ with array_positions

Using basic PostgreSQL array functions we call string_to_array(), and then feed that to array_positions() like this 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;

Upvotes: 5

lub0v
lub0v

Reputation: 959

Another way to count last position is to slit string to array by delimeter equals to needed character and then substract length of characters for the last element from the length of 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 is easier to use

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

Upvotes: 0

Autumn Gao
Autumn Gao

Reputation: 59

In the case where char = '.', an escape is needed. So the function can be written:

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

Upvotes: 5

leonbloy
leonbloy

Reputation: 76026

This pure SQL function will provide the last position of a char inside the string, counting from 1. It returns 0 if not found ... But (big disclaimer) it breaks if the character is some regex metacharacter ( .$^()[]*+ )

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

A more robust solution would involve pl/pgSQL, as rfusca's answer.

Upvotes: 3

rfusca
rfusca

Reputation: 7725

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;

Upvotes: 39

Related Questions