validate a numeric column postgres-CodePudding

im trying to validate a column using postgresql where values in the column are (0000-ASZAS) four numerical values-five alphbets

SELECT invoice_number,
CASE
WHEN invoice_number = '[0-9][0-9][0-9][0-9]-[A-Z][A-Z][A-Z][A-Z][A-Z]' 
THEN 'valid'
ELSE 'invalid'
END
from invoices;

also tried LIKE instead of =

sorry wrong column customer_id with alpha numeric values invoice_number is numeric. thank you for the correction

CodePudding user response：

Try ~ or similar_to. See functions-matching

WHEN invoice_number ~ '[0-9][0-9][0-9][0-9]-[A-Z][A-Z][A-Z][A-Z][A-Z]'

CodePudding user response：

You may use the ~ POSIX regex operator:

SELECT invoice_number,
       CASE WHEN invoice_number ~ '^[0-9]{4}-[A-Z]{5}$' 
            THEN 'valid'
            ELSE 'invalid' END
FROM invoices;

CodePudding user response：

im cleaning a data and want to return 0 rows so it doesnt crowd the results so this is what i did...

SELECT customer_id FROM invoices WHERE customer_id !~ '[0-9][0-9][0-9][0-9]-[A-Z][A-Z][A-Z][A-Z][A-Z]';

w/c gives me exactly what i want. Would this be as strong/valid for cleaning compared to using like or = ?