CODEWITHSUNDEEP
postgresql
lemba
lemba

Reputation: 21

Truncating leading zero from the string in postgresql

I'm trying to truncate leading zero from the address. example:

input 

1 06TH ST
12 02ND AVE
123 001St CT

expected output

1 6TH ST
12 2ND AVE
123 1St CT

Here is what i have:

update table
set address = regexp_replace(address,'(0\d+(ST|ND|TH))','?????? need help here')
where address ~ '\s0\d+(ST|ND|TH)\s';

many thanks in advance

Upvotes: 2

Views: 3246

Answers (2)

pozs
pozs

Reputation: 36244

What you are looking for is the back references in the regular expressions:

UPDATE table
   SET address = regexp_replace(address, '\m0+(\d+\w+)', '\1', 'g')
 WHERE address ~ '\m0+(\d+\w+)'

Also:

  • \m used to match the beginning of a word (to avoid replacing inside words (f.ex. in 101Th)
  • 0+ truncates all zeros (does not included in the capturing parenthesis)
  • \d+ used to capture the remaining numbers
  • \w+ used to capture the remaining word characters
  • a word caracter can be any alphanumeric character, and the underscore _.

Upvotes: 1

Greg
Greg

Reputation: 6759

assuming that the address always has some number/letter address (1234, 1a, 33B) followed by a sequence of 1 or more spaces followed by the part you want to strip leading zeroes...

select substr(address, 1, strpos(address, ' ')) || ltrim(substr(address, strpos(address, ' ')), ' 0') from table;

or, to update the table:

update table set address = substr(address, 1, strpos(address, ' ')) || ltrim(substr(address, strpos(address, ' ')), ' 0');

-g

Upvotes: 1

Related Questions