Compare two VAT numbers

Question

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 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: If you

Accepted 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:CASEWHEN REGEXP_LIKE( o.VAT_NUMBER, '^d{9}[A-Z]{2}d{4}$')THEN o.VAT_NUMBERELSE NULLEND as VAT_NUMBERIf 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.

Advertisement

Answer