Question 90745 in PostgreSQL Question posted in
The official documentation can be found here.

validate a numeric column postgres – Postgresql

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

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I'm cleaning a data and want to return 0 rows so it doesn't 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 = ?


  2. 0

    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]'
    Login or Signup to reply.
  3. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search