Question 268737 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – how can regexp sub replace preceding zeros of stream of digits while keeping the last zero of a stream of all zeros (postgres)

I want to normalize a hex number that’s stored in a varchar col of a PG table by eliminating preceding zeros (I need to compare vals that may be represented differently). Easy enough. But I also want to preserve the last zero if the stream of digits is all zeros. Example…

create table foo (col1 varchar);
insert into foo (col1) values ('''h000123abc'),('''h123abc'),('''h0123abc'),('''h000000000');
select regexp_replace(regexp_replace(col1,'''h',''),'^0+','') from foo;
 regexp_replace 
----------------
 123abc
 123abc
 123abc

(4 rows)

As you can see, the actual hex val has a "’h" prefix which I had to get rid of.
I want to preserve a single 0 in the case where the val is 0. I want that last row to be ‘0’.

I’ll file this one under both PG and regexp and regexp-replace because I expect the straight regexp soln would map OK to the SQL func regexp_replace.

Thanks for any help

Tags:

2

Answers


  1. 0

    Use a CASE expression to perform different replacements depending on whether the value is all 0 or not.

    SELECT CASE
        WHEN REGEXP_LIKE(col1, '^''h0+$') THEN '0'
        ELSE REGEXP_REPLACE(col1, '^''h0*', '')
    END AS new_col1
FROM foo

    DEMO

    There’s also no need to use separate calls to REGEXP_REPLACE() to remove the 'h and the leading zeros.

    Login or Signup to reply.
  2. 0

    You could use a regexp which matches a leading 'h with 0‘s up to at least one character before end of line (which will prevent removing all 0s in 'h0000) and replacing the match with an empty string:

    SELECT REGEXP_REPLACE(col1, '^''h0*(?=.+$)', '')
FROM foo

    Output for your sample data:

    regexp_replace
    123abc
    123abc
    123abc
    0

    Demo on db-fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search