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