regex – 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

Read more here: Source link