Re: Posix the tool to use?

On Mon, Oct 8, 2012 at 12:39 AM, Romain Billon-Grand <rbgrbg(at)free(dot)fr> wrote:
> 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}$'

> 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