Postgresql: Extracting substring after first instance of delimiter

Question

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:

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

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

Caius Jard · Accepted Answer · 2018-12-27 17:27:00Z

3

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)

edited Dec 27, 2018 at 17:27

answered Dec 27, 2018 at 17:21

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2018-12-27 17:22:53Z

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.

GMB · Accepted Answer · 2018-12-27 17:29:03Z

1

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

edited Dec 27, 2018 at 17:29

answered Dec 27, 2018 at 17:23

GMB

224k25 gold badges102 silver badges151 bronze badges

Comments

Himanshu · Accepted Answer · 2018-12-27 17:51:18Z

0

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 ;

answered Dec 27, 2018 at 17:51

Himanshu

3,9802 gold badges15 silver badges34 bronze badges

Collectives™ on Stack Overflow

Postgresql: Extracting substring after first instance of delimiter

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related