Compare two VAT numbers-CodePudding

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 :(

CodePudding user response：

I'm not familiar with VAT numbers you use. If I got it correctly, it seems that value you're looking for contains 9 digits followed by 2 Ls (?) followed by 4 digits. Oracle docs say that "L" stands for local currency symbol (that's RP in your case), so - for sample data

SQL> with test (vat_number) as
  2    (select '786633632R97685' from dual union all
  3     select '121015206R77405' from dual union all
  4     select '567299566RP9332' from dual
  5    )

you could try

  6  select vat_number,
  7         case when regexp_like(vat_number, '\d {9}RP\d {4}') then vat_number
  8              else 'null'
  9         end as result
 10  from test;

VAT_NUMBER      RESULT
--------------- ---------------
786633632R97685 null
121015206R77405 null
567299566RP9332 567299566RP9332

SQL>

CodePudding user response：

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.