Question

Oracle SQL pattern matching using wildcards

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.

 2  25  2
1 Jan 1970

Solution

 1

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
2024-07-22
Thorsten Kettner