Tuesday, July 19, 2011

Oracle SQL - Formatting zip code

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