Posix the tool to use?

[Romain Billon-Grand]

Good eveningAs a novice with postgres, I am not even sure to be on the good way for probably quite a basic query...I am trying to retrieve all rows where one of the text column is a code made of 4 letters followed by 3 numbers such as "LFFA002" or "QSZT123", and of course only them. Other rows from the table are empty, or containing some short sentences, or single letters between brackets. To heterogeneous to be a way to take the problem the other side (that is why I need to clean this ~26000 row table...). But I barely understand anything to Posix or regular expressions in the postgres documentation, even though I feel those could be the tools I need for my WHERE statement...Would you please tell me how to code this? Or better, help me finding a tutorial I could understand(=dummy-adapted!!)? (Even better if it is in French or Spanish, far more natural than english for me!)BestsRomain Billon-Grand

[Josh Kupershmidt]

Post by Romain Billon-Grand
Good evening
As a novice with postgres, I am not even sure to be on the good way for
probably quite a basic query...
I am trying to retrieve all rows where one of the text column is a code made
of 4 letters followed by 3 numbers such as "LFFA002" or "QSZT123", and of
course only them. Other rows from the table are empty, or containing some
short sentences, or single letters between brackets. To heterogeneous to be
a way to take the problem the other side (that is why I need to clean this
~26000 row table...). But I barely understand anything to Posix or regular
expressions in the postgres documentation, even though I feel those could be
the tools I need for my WHERE statement...
Would you please tell me how to code this?

I think this POSIX regex would do the trick for matching exactly 4
letters followed by exactly 3 numbers, with nothing else at the start
or end of the string:

'^[A-Za-z]{4}[[:digit:]]{3}$'

Post by Romain Billon-Grand
Or better, help me finding a
tutorial I could understand(=dummy-adapted!!)? (Even better if it is in
French or Spanish, far more natural than english for me!)

Section 9.7.3 of the Postgres docs is a good place to start, albeit in English:

http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP

There are many books out there which give more comprehensive
explanations of the subject, e.g. "Mastering Regular Expressions".
Also, the Postgres community has separate French and Spanish language
lists if you prefer:

http://www.postgresql.org/community/lists/

Josh