CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlactiverecord
bilal
bilal

Reputation: 219

SQL: how to check if column contains letters in it

So basically i have an ic column in my db: ic only contains numbers but the field is a string so people can add passport also. i want to write a query where it only shows me ic which are only numbers:

SELECT applications.ic
FROM applications
WHERE applications LIKE '%[^0-9]%'

But it dosent work!

Upvotes: 0

Views: 1088

Answers (1)

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121000

PostgreSQL supports POSIX regular expressions:

SELECT applications.ic
FROM applications
WHERE applications.ic ~ '^\d+$'

The regular expression here is:

  • ^ — matches the beginning of the string
  • \d — matches a digit
  • + — one or more modifier applied to the previous matcher
  • $ — matches the end of the string

Upvotes: 8

Related Questions