CODEWITHSUNDEEP
postgresql
Zinda Hu
Zinda Hu

Reputation: 1

removing leading zero and hyphen in Postgres

I need to remove leading zeros and hyphens from a column value in Postgresql database, for example:

121-323-025-000 should look like 12132325 060579-0001 => 605791 482-322-004 => 4823224

timely help will be really appreciated.

Upvotes: 0

Views: 11237

Answers (1)

gwaigh
gwaigh

Reputation: 1302

Postgresql string functions. For more advanced string editing, regular expressions can be very powerful. Be aware that complex regular expressions may not be considered maintainable by people not familiar with them.

CREATE TABLE testdata (id text, expected text);
INSERT INTO testdata (id, expected) VALUES
    ('121-323-025-000', '12132325'),
    ('060579-0001', '605791'),
    ('482-322-004', '4823224');
SELECT id, expected, regexp_replace(id, '(^|-)0*', '', 'g') AS computed
  FROM testdata;

How regexp_replace works. In this case we look for the beginning of the string or a hyphen for a place to start matching. We include any zeros that follow that as part of the match. Next we replace that match with an empty string. Finally, the global flag tells us to repeat the search until we reach the end of the string.

Upvotes: 4

Related Questions