CODEWITHSUNDEEP
regexpostgresql
Trocader
Trocader

Reputation: 85

PostgreSQL Regular Expression - Excluding strings with numbers

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.

Upvotes: 8

Views: 30259

Answers (3)

Cosmin Gruian
Cosmin Gruian

Reputation: 61

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

Upvotes: 1

user2478690
user2478690

Reputation: 441

Another simple solution: 

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

Upvotes: 4

Ashalynd
Ashalynd

Reputation: 12563

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]*$.

Upvotes: 27

Related Questions