I'm trying to search though a large list of postal codes in various countries Specifically, I want to identify all Canadian entries USING A CASE STATEMENT. after all spaces have been stripped out, Canadian Postal Codes should conform to this pattern:
[A-Z][0-9][A-Z][0-9][A-Z][0-9]
Something like
CASE
WHEN AD.AD_PCODE LIKE '[A-Z][0-9][A-Z][0-9][A-Z][0-9]' THEN 'CANADA'
does Oracle SQL support this sort of pattern matching? I know SQL does, but I cant find any references to doing this sort of thing with Oracle SQL.
In Oracle we use REGEXP_LIKE:
CASE
WHEN REGEXP_LIKE(ad.ad_pcode, '[A-Z][0-9][A-Z][0-9][A-Z][0-9]') THEN 'CANADA'
...
END