PostgreSQL Regular Expression - Excluding strings with numbers

Question

I am starting with Postgre Regular Expressions, working on PostgreSQL 8.3 version.

I have a table of records as following:

record
-----------
a  
ab
abc  
123abc 
132abc
123ABC  
abc123
4567  
5678
6789

I was going through a simple tutorial: www.oreillynet.com. Everything seemed clear to me until I came to the following query:

SELECT record FROM myrecords WHERE record ~ '[^0-9]';

The tutorail says:

this expression returns strings that don't include digits.

But it returned following output to me:

output
------
a
ab
abc
123abc
132abc
123ABC
abc123

Can anyone explain me this behaviour, please? I am then struggling with another expression on that site trying to exclude strings that include digits AND lower-case letters in the same string.

Wrong: it returns strings that contains one or more non-digit character. — wildplasser
– wildplasser, Commented Mar 16, 2014 at 12:13

Ashalynd · Accepted Answer · 2014-03-16 12:20:34Z

29

This command:

SELECT record FROM myrecords WHERE record ~ '[^0-9]';

means that in the record field there should be at least one non-digit character (this is the meaning of the regex).

If one looks for the records which would include digits and lower-case letter, then I would expect a regex like:

SELECT record FROM myrecords WHERE record ~ '[0-9a-z]';

which would return all the records having at least one character which is a digit or lowercase letter.

If you want to get the records which have no digits, then you would have to use the following regex:

SELECT record FROM myrecords WHERE record ~ '^[^0-9]+$';

Here, the ^ character outside of square brackets means the beginning of the field, the $ character means the end of the field, and we require that all characters in between are non-digits. + indicates that there should be at least one such characters. If we would also allow empty strings, then the regex would look like ^[^0-9]*$.

answered Mar 16, 2014 at 12:20

Ashalynd

12.6k2 gold badges36 silver badges38 bronze badges

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

1 Comment

Trocader Over a year ago

Thank you very much for your detailed answer! Now, everything is clear!

user2478690 · Accepted Answer · 2014-03-17 15:19:17Z

5

Another simple solution:

SELECT record FROM myrecords WHERE record !~ '[0-9]';

answered Mar 17, 2014 at 15:19

user2478690

4512 silver badges4 bronze badges

Comments

Cosmin Gruian · Accepted Answer · 2019-03-20 09:44:02Z

1

select 'a2' ~ '^[a-z]+$'; --false
select 'a' ~ '^[a-z]+$'; --true

'^[a-z]+$' => checks only letters from a-z from the beginning(^) till the end of the string (+$)

If you want to check for numbers: '^[0-9]+$'

If you want to check for numbers and a character, lets say "." :'^[0-9\.]+$' , where "\" is an escape character

answered Mar 20, 2019 at 9:44

Cosmin Gruian

613 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Regular Expression - Excluding strings with numbers

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related