Search code examples
sqloracle-databaseoracle11g

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.sstatic.net/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 :(

Solution

  • 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.