CODEWITHSUNDEEP
regexpostgresqlregex-lookaroundsregexp-replace
moonshot
moonshot

Reputation: 699

Positive Lookbehind in Postgres 9.5 using regexp_replace

I have values like this: 1ST, 2ND, FIRST, and I want to remove the 'ST' and 'ND' ONLY if what comes before is a digit.

I am running postgres 9.5 and I have a positive lookbehind working in SQL Fiddle but it only works on 9.6

SELECT ex,
regexp_replace(ex, '(?<=[0-9]+)(TH|ST|ND|RD)', '', 'gi') as test 
FROM t1

Is there any other way to do this besides using a CASE statement like this:

SELECT ex,
(CASE WHEN ex ~ '\d(TH|ST|ND|RD)' THEN regexp_replace (ex, 'TH|ST|ND|RD', '','gi') ELSE ex end) as test_case
FROM t1

Any suggestions would be appreciated. Thanks!

Upvotes: 1

Views: 456

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627292

You may match and capture the digit and replace with a backreference to the value. Also, I suggest adding a word boundary after the ordinal numeral suffixes to make sure we are matching them at the end of the word.

SELECT regexp_replace(ex, '([0-9])(?:TH|ST|ND|RD)\y', '\1', 'gi') as test_case FROM t1

See the updated SQLFiddle.

CREATE TABLE t1
    (ex varchar)
;

INSERT INTO t1
    (ex)
VALUES
    ('1ST'),
    ('2ND'),
    ('3RD'),
    ('4TH'),
    ('FIRST'),
    ('FOURTH')
 ;

SELECT regexp_replace(ex, '([0-9])(?:TH|ST|ND|RD)\y', '\1', 'gi') as test_case FROM t1

enter image description here

Upvotes: 1

Related Questions