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

Postgresql – How to find rows with only 8 numbers as value

I want to find rows that not contains exactly 8 numbers in Ident column, everything non numeric, white space, less numbers, more numbers.. From column in PostgreSQL. From table below I want get everything without row ID 5

ID Ident
1 FN033741
2 NZ244/20
3 001091
4 01-09-16
5 06494824
6 102

I have tried this but don’t know how to identified the rows with exactly 8 numbers.

where ident !~ ‘^[-+]?[0-9]*.?[0-9]+([eE][-+]?[0-9]+)?$’

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I used this one

    SELECT *
FROM your_table
WHERE ident ~ '[^0-9]' OR
      (length(ident) <> 8 AND ident ~ '.*[0-9].*')

  2. 0

    We can use a regular expression that matches anyy non-numeric characters and then count the number of numeric expressions. Applying this query might help:

    SELECT *
FROM your_table
WHERE Ident ~ '^[^0-9]*[0-9][^0-9]*$' AND
      LENGTH(REGEXP_REPLACE(Ident, '[^0-9]', '', 'g')) != 8;
    Login or Signup to reply.
  3. 0

    Try this query, I have tested it here:

    SELECT *
FROM your_table
WHERE ident !~ '^d{8}$';
    • ^d{8}$ is a regular expression that matches exactly 8 digits.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search