Compare two VAT numbers

I`m trying to list only the vat numbers which look like DDDDDDDDDLLDDDD else i have to list NULL https://i.stack.imgur.com/4PYR8.png

case when o.VAT_NUMBER= TO_char(o.VAT_NUMBER, '999999999LL9999') then o.VAT_NUMBER
    else 'NULL' end as VAT_NUMBER,

Pretty sure that I`m close but still missing something 🙁

Answer

You can use the regular expression ^d{9}[A-Z]{2}d{4}$ to match the start-of-the-string, then any 9 digits, then any 2 upper-case letters, then any 4 digits and, finally, the end-of-the-string:

CASE
WHEN REGEXP_LIKE( o.VAT_NUMBER, '^d{9}[A-Z]{2}d{4}$')
THEN o.VAT_NUMBER
ELSE NULL
END as VAT_NUMBER

If you want any case then use [a-zA-Z] or [[:alpha:]] or make the match case-insensitive.

Note: If you do not include the start- and end-of-the-string in the match then you could match a sub-string in the middle of the string and not the entire string.