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

How can I return all numeric values in PostgreSQL?

I had to select from the column all the values that contain numbers (NULL values aren’t interested). How can I make a query to my table using something like this:

SELECT c1
FROM t1 
WHERE c1 ...;

I tried to find suitable regular expressions, which are popular in many imperative programming languages, but they didn’t fit for PostgreSQL, unfortunately (or I used them incorrectly)

Tags:

4

Answers


  1. 0

    Possible solution:

    SELECT c1 FROM t1 WHERE c1 ~ '^[-+]?[0-9]*.?[0-9]+([eE][-+]?[0-9]+)?$';
    Login or Signup to reply.
  2. 0

    From Pattern Matching:

    
SELECT
    fld
FROM (
    VALUES ('1'),
        ('test1'),
        ('34'),
        ('dog'),
        ('3cat')) AS t (fld)
WHERE
    regexp_match(fld, '^[[:digit:]]{1,}$') IS NOT NULL;

fld 
-----
 1
 34
    Login or Signup to reply.
  3. 0

    Another possibility:

    SELECT c1 FROM t1 WHERE c1 ~ '[0-9]';

    From this table named STACKOVERFLOW:

    id_stack name
    1 Niklaus Wirth
    2 Bjarne Stroustrup
    3 Linus Torvalds
    4 Donald Knuth
    5 C3PO
    6 R2D2
    7 ZX Spectrum +3

    The Query SELECT NAME FROM STACKOVERFLOW WHERE NAME ~ ‘[0-9]’; will return:

    name
    C3PO
    R2D2
    ZX Spectrum +3
    Login or Signup to reply.
  4. 0 
    SELECT * FROM <table> WHERE <column> ~ 'PRF:d{11}-d{4}'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search