Postgres SQL regexp_replace replace all number

Question

I need some help with the next. I have a field text in SQL, this record a list of times sepparates with '|'. For example

'14613|15474|3832|148|5236|5348|1055|524' Each value is a time in milliseconds. This field could any length, for example is perfect correct '3215|2654' or '4565' (only 1 value). I need get this field and replace all number with -1000 value.

So '14613|15474|3832|148|5236|5348|1055|524' will be '-1000|-1000|-1000|-1000|-1000|-1000|-1000|-1000'

Or '3215|2654' => '-1000|-1000' Or '4565' => '-1000'.

I try use regexp_replace(times_field,'[[:digit:]]','-1000','g') but it replace each digit, not the complete number, so in this example:

'3215|2654' than must be '-1000|-1000', i get:

'-1000-1000-1000-1000|-1000-1000-1000-1000', I try with other combinations and more options of regexp but i'm done.

Please need your help, thanks!!!.

Tim Biegeleisen · Accepted Answer · 2020-09-07 23:53:37Z

2

We can try using REGEXP_REPLACE here:

UPDATE yourTable
SET times_field = REGEXP_REPLACE(times_field, '\y[0-9]+\y', '-1000', 'g');

If instead you don't really want to alter your data but rather just view your data this way, then use a select:

SELECT
    times_field,
    REGEXP_REPLACE(times_field, '\y[0-9]+\y', '-1000', 'g') AS times_field_replace
FROM yourTable;

Note that in either case we pass g as the fourtb parameter to REGEXP_REPLACE to do a global replacement of all pipe separated numbers.

answered Sep 7, 2020 at 23:53

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Comments

Laurenz Albe · Accepted Answer · 2020-09-08 02:42:53Z

1

[[:digit:]] - matches a digit [0-9]

+ Quantifier - matches between one and unlimited times, as many times as possible

your regexp must look like

regexp_replace(times_field,'[[:digit:]]+','-1000','g')

edited Sep 8, 2020 at 2:42

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Sep 7, 2020 at 23:51

cetver

11.9k6 gold badges42 silver badges59 bronze badges

1 Comment

Alvaro Over a year ago

Thanks a lot!!. It works and was so simple, I really give you +1 votes, but i can't for my reputation. "Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score." :(

Collectives™ on Stack Overflow

Postgres SQL regexp_replace replace all number

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related