help with nvl and substr
526272Feb 27 2007 — edited Feb 27 2007Hi,
Somebody please help me how to phrase this query. I want to use nvl and substr of zipcode. I want to display only the 5 characters for zipcode for eg. 33265 instead of 33265-5642.
My query is:
select * from A
where current_city = NVL(:city, current_city)
AND current_state = NVL(:state, current_state)
AND current_zipcode = NVL(:zip, substr(current_zipcode,1,5)).
When I run the above query, it shows me only those records which has 5 digit zipcodes and does not show me the other records which have 5 digit plus the 4 digits (the table has mixed of 5 digit zipcodes and 5 digit+4 zipcode).
Please tell me how to phrase my query for zipcode.
Thanks.