CODEWITHSUNDEEP
sqlpostgresqlreplaceregexp-replace
Alvaro
Alvaro

Reputation: 73

Postgres SQL regexp_replace replace all number

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!!!.

Upvotes: 2

Views: 1957

Answers (2)

cetver
cetver

Reputation: 11829

[[: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')

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521008

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.

Upvotes: 2

Related Questions