There is a requirement to display a formatted US Zip code on screen from a oracle view given below, the zip code should be formatted XXXXX-XXXX only if country code is US and length of zip-code is 9-digits.
CREATE OR REPLACE VIEW PARTY_ADDR AS SELECT ZIP_CODE AS ZIP_POSTAL_CODE,COUNTRY_CD,ADDRESS_LINE_1 FROM PARTY 4 Answers
First off, I will assume that the field is varchar as many country postal codes are a mix of numbers and letters.
So if you are insisting on doing this in your query, something like:
SELECT CASE WHEN country_code = 'US' and length(postal_code) = 9 THEN substr(postal_code,1,5)||'-'||substr(postal_Code(6,4) else postal_Code end as disply_postal_Code from your_Table Here's the code I use and it works great every time.
'ZipCode' = case LEN(LTRIM(RTRIM(zipcode))) when 9 then STUFF(zipcode, 6, 0, '-') else zipcode end REGEXP_REPLACE( ) to the rescue! Of course add your other WHERE clause criteria of country code, etc.
SQL> with tbl(zip) as ( select '12345' from dual union select '12345-1234' from dual ) select REGEXP_REPLACE(ZIP,'(\d{5})(\d{4})','\1-\2') ZIP from tbl; ZIP ------------------------------------------------------------ 12345 12345-1234 SQL> Note that there is a possible caveat that should be mentioned. This works because if the length is 9 digits, it gets broken into 2 sets, one of 5 and one of 4 and displayed with a dash in between. Anything else and the regex is not matched so the original string is returned, including bad data. Add some more test data to the WITH clause as well as doing some sanity checks on your data first to see if you have any bad data that needs to be cleaned. Good thing to do anyway I'd say.
Just an alternative. Instead of REGEXP we can also use OLDSCHOOL methods :P like SUBSTR as we have to only format US based zips. As you have provided that zip for US will be 9 digits only so based on that this code will help you too.
WITH tbl AS ( SELECT '12345' ZIP,'IN' country FROM dual UNION SELECT '123451234' ZIP, 'US' country FROM dual UNION SELECT '123456' zip,'KR' country FROM dual ) SELECT DECODE(TBL.country,'US',SUBSTR(tbl.zip,1,5) ||'-' ||SUBSTR(tbl.zip,6,LENGTH(tbl.zip)),TBL.ZIP) zip_formatted FROM tbl ; --------------------------------OUTPUT--------------------------------------------- DECODE(TBL ---------- 12345 12345-1234 123456 Elapsed: 00:00:06.17 ----------------------------------------------------------------------------------- 
