current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
1  
Wrong: it returns strings that contains one or more non-digit character. –  wildplasser Mar 16 at 12:13
1  
Thanks very much, maybe not very good tutorial. –  Trocader Mar 16 at 12:18
add comment

2 Answers

up vote 1 down vote accepted

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

share|improve this answer
    
Thank you very much for your detailed answer! Now, everything is clear! –  Trocader Mar 16 at 12:25
    
you're welcome :) –  Ashalynd Mar 16 at 12:29
add comment
up vote 1 down vote

Another simple solution: 

SELECT record FROM myrecords WHERE record !~ '[0-9]';
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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