CODEWITHSUNDEEP
sqlpostgresql
Wessi
Wessi

Reputation: 1822

Postgresql: Extracting substring after first instance of delimiter

I'm trying to extract everything after the first instance of a delimiter. For example: 

01443-30413 -> 30413
1221-935-5801 -> 935-5801

I have tried the following queries:

select regexp_replace(car_id, E'-.*', '') from schema.table_name;
select reverse(split_part(reverse(car_id), '-', 1)) from schema.table_name;

However both of them return:

So it's not working if delimiter appears multiple times.

I'm using Postgresql 11. I come from a MySQL background where you can do:

select SUBSTRING(car_id FROM (LOCATE('-',car_id)+1)) from table_name

Upvotes: 1

Views: 2239

Answers (4)

Himanshu
Himanshu

Reputation: 3970

I tried it in a conventional way in general what we do (found something similar to instr as strpos in postgrsql .) Can try the below 

    SELECT
     SUBSTR(car_id,strpos(car_id,'-')+1,
     length(car_id) ) from table ;

Upvotes: 0

GMB
GMB

Reputation: 222722

Use this regexp pattern :

select regexp_replace('1221-935-5801', E'^[^-]+-', '') from schema.table_name

Regexp explanation :

  • ^ is the beginning of the string
  • [^-]+ means at least one character different than -
  • ...until the - character is met

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74740

Why not just do the PG equivalent of your MySQL approach and substring it?

SELECT SUBSTRING('abcdef-ghi' FROM POSITION('-' in 'abcdef-ghi') + 1)

If you don't like the "from" and "in" way of writing arguments, PG also has "normal" comma separated functions:

SELECT SUBSTR('abcdef-ghi', STRPOS('abcdef-ghi', '-') + 1)

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

I think that regexp_replace is appropriate, but using the correct pattern:

select regexp_replace('1221-935-5801', E'^[^-]+-', '');

935-5801

The regex pattern ^[^-]+- matches, from the start of the string, one or more non dash characters, ending with a dash. It then replaces with empty string, effectively removing this content.

Note that this approach also works if the input has no dashes at all, in which case it would just return the original input.

Upvotes: 1

Related Questions