I need to check whether a column in a table having a numeric value followed by decimal point and 3 precisions after the decimal point. Kindly suggest how to do using regular expression in postgre SQL (or) any other alternate method.
Thanks
Add a comment
|
1 Answer
The basic regex for digits, a period and digits is \d+\.\d{3}
You can use it for several things, for instance:
1. Add a Constraing to your Column Definition
ALTER TABLE mytable ADD (CONSTRAINT mycolumn_regexp CHECK (mycolumn ~ $$^\d+\.\d{3}\Z$$));
2. Find Rows that Don't Match
SELECT * FROM mytable WHERE mycolumn !~ $$^\d+\.\d{3}\Z$$;
3. Find Rows that Match
SELECT * FROM mytable WHERE mycolumn ~ $$^\d+\.\d{3}\Z$$;
2 Comments
toraritte
Is my understanding correct, that a single quote (
') could've been used instead of $$, because they are interchangeable? (from What are '$$' used for in PL/pgSQL and 4.1.2.4. Dollar-Quoted String Constants.)toraritte
The link from the PostgreSQL manual that helped me decipher the matching part: 9.7.3. POSIX Regular Expressions in "9.7 Pattern Matching".