I'm using Postgres 9.5. I have a name column of type "character varying". I wonder how to select rows where the column begins with a number, followed by a space, followed by any other data.

So I would want rows where the column looks like:

1 abcDEF
12345 LAST

But not when it looks like:


1 Answer 1


Use a regular expression:

FROM   tbl
WHERE  name ~ '^\d+ .+';

The regular expression explained:

^ .. start of string
\d .. class shorthand for digits
+ .. 1 or more of the preceding atom
.. plain space
.+ .. any character, again 1 or more of those

There are many different characters that look like a space. ' ' is just the plain space character. You might want to use the class shorthand \s instead to cover all of them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.