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?
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
length of more than 5or5 or more numbers, which will it be? Assuming "5 or more" ...