Filtering Croatian Plate Numbers on Oracle SQL

Question

So, I have a list of plate numbers from different countries. If I want to filter Croatian plates, how can I do that? Croatian plate formats are like ZG 000-A, ZG 000-AA, ZG 0000-A or ZG 0000-AA. (Ignore the dashes)

I’ve tried several methods but can’t really filter the plates.

I have tried to filter according to first two letters that Croatia using and the plate lenght.

Answer 1

Assuming that you want:

• The start of the string (^)
• Two letters ([A-Z]{2})
• Followed by space ( )
• Followed by three or four digits ([0-9]{3,4} or \d{3,4})
• Followed by an optional dash (-?)
• Followed by one or two letters ([A-Z]{1,2})
• Followed by the end of the string ($).

Then you can use a regular expression:

SELECT *
FROM   table_name
WHERE  REGEXP_LIKE(plate_number, '^[A-Z]{2} [0-9]{3,4}-?[A-Z]{1,2}$')

Which, for the sample data:

CREATE TABLE table_name (plate_number) AS
SELECT 'ZG 000-A'   FROM DUAL UNION ALL
SELECT 'ZG 000-AA'  FROM DUAL UNION ALL
SELECT 'ZG 0000-A'  FROM DUAL UNION ALL
SELECT 'ZG 0000-AA' FROM DUAL UNION ALL
SELECT 'ABC00XY' FROM DUAL UNION ALL
SELECT '01234' FROM DUAL;

Outputs:

PLATE_NUMBER
ZG 000-A
ZG 000-AA
ZG 0000-A
ZG 0000-AA

fiddle