This code will format zip code in the format xxxxx-xxx..., using regular expression.
Steps involved:
1) first removes any non-numeric characters
2) if the lenght is greater than 5 characters, introduces a '-' after first 5 characters.
3) returns the non-numeric zip, otherwise.
select decode(ZIP,NULL,'', CHR(13)
||
case when length(REGEXP_REPLACE(ZIP, '[^0-9]+', '')) > 5
then
REGEXP_REPLACE(REGEXP_REPLACE(ZIP, '[^0-9]+', ''),'(.....)(.*)','\1-\2')
else
REGEXP_REPLACE(ZIP, '[^0-9]+', '')
end
)
from address;
No comments:
Post a Comment