Find not-integer values in a string column

Question

How to select records which are having non-integer values in a particular column?

I tried like:

SELECT * FROM tableName WHERE status !~ '^\d+?\$'

I want to find all records not storing exact integer representations.

  Column   |         Type           | Modifiers 
------------+-----------------------+-----------
 status    | charecter varying(25)  |

khampson · Accepted Answer · 2014-11-21 06:31:05Z

7

Since you're looking for non-integer values, if the status contains anything that isn't a digit (i.e. a letter, decimal point, etc.), it's not an integer, so this regex should work:

select * from foo where status ~ E'[^\\d]'

Note the double-escape of the backslash and the use of the negated character class.

Here's an sqlfiddle.

edited Nov 21, 2014 at 6:31

answered Nov 21, 2014 at 6:25

khampson

15.5k4 gold badges45 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

You don't need the double backslash if you use a standard SQL character literal: '[^\d]'

Erwin Brandstetter · Accepted Answer · 2023-06-15 02:29:20Z

5

Postgres uses signed integer, we need to allow an optional leading minus or plus sign (+-). Not valid as integer literals:

SELECT * FROM tbl WHERE status !~ '^[+-]*\d+$'

Note that leading and trailing whitespace is tolerated and trimmed, but not nested whitespace.

fiddle
_{Old sqlfiddle.}

edited Jun 15, 2023 at 2:29

answered Nov 21, 2014 at 14:28

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Find not-integer values in a string column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest