CODEWITHSUNDEEP
regexpostgresql
user3357159
user3357159

Reputation: 1

Postgres regular expression for a number of elements

I am trying to write a Postgres regular expression to find records where a column has numbers only and a string length of more than 5.

E.g.: column hello has '0 1 1 2 1' or '12345'.

Or to keep it simple I want a regular express that will identify rows that has 5 or more numbers in it.

How do I write that regex?

Upvotes: 0

Views: 1447

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657797

SELECT *
FROM   (
   VALUES 
    ('98765')
   ,('1 2 3 4 5')
   ,('143562465')
   ,(' 1 2 5  3 235')
   ,(' 1 2 5  3 235s')
   ,('y 1 2 5  3 235')
   ,('245no')
   ,('1234')
    ) sub(hello)
WHERE  hello ~ '^[\d ]+$'
AND    length(translate(hello, ' ','')) > 4

Result:

98765
1 2 3 4 5
143562465
 1 2 5  3 235

Explain regular expression:

\d .. regexp shorthand for [digit] character class
[\d ] .. digits or space
^ .. start of sting
$ .. end of string

And translate() is the fastest method to replace single characters, just space in this example.

Of if you are looking for a single regular expression (probably slower than the above):

WHERE hello ~ '^ *(\d *){5,}$'

Explain:
* .. zero or more spaces
(\d *) .. an atom consisting of a digit followed by zero or more space chars
{5,} .. the previous atom 5 or more times

Upvotes: 1

Related Questions