Re: Using regular expressions in LIKE - Mailing list pgsql-general

> Hi Terry & Nick,
> thank you very much for your help. My lack of comprehension is because
of
> my
> lack of knowladge of regular expressions.
>
> =====================================================
> tgr=# \d t_me30
>                 Table "public.t_me30"
>     Column    |           Type           | Modifiers
> --------------+--------------------------+-----------
>  fomeazon     | integer                  |
>  mertido      | character(16)            |
>  ertektipus   | character(10)            |
>  hetnap       | character(1)             |
>  impulzusszam | double precision         |
>  mertertek    | double precision         |
>  merttartam   | integer                  |
>  utmodido     | timestamp with time zone |
> Indexes:
>     "idx_t_me30_ertektipus" btree (ertektipus)
>     "idx_t_me30_fomeazon" btree (fomeazon)
>     "idx_t_me30_mertido" btree (mertido)
>     "idx_t_me30_mertido2" btree (mertido bpchar_pattern_ops)
>     "idx_t_me30_utmodido" btree (utmodido)
> =====================================================
>
> 1. Using Terry's query it didn't work because I tried to used LIKE's
<any
> one character> operator "_":
>    select * from t_me30 where mertido ~ '2003-12-17___:(15|30)'; -- It
> results an empty set.
>
> 2. Using Nick's query "select * from t_me30 where mertido ~
'^2003-12-17
> ([0-9]{2}):(15|30)';" it worked fine and fast.
>
> Nick, I can understand now the meaning of your regular expression.
Just a
> question: why is it required to indicate the begining of the value by
"^"?
> Wouldn't it be clear for the interpreter.
> Is there any other way (simpler) to indicate that 3-4 irrelevant
character
> in the centre of the value - I mean something like I tried first
("_")?
>

If the ^ was not there then it could theoretically match anywhere in the
string. In this particular case the regular expression will probably
match at the beginning of the string anyway, so it is not really
necessary. I prefer to put that sort of thing in to make it clear to the
programmer what is going on.

To make the query more generic you could replace any of the numbers in
the date part with a ([0-9]{n}) bit. So for instance:

All dates whose minutes = 15 or 30 and whose year is 2003 and whose
month is 12:

select * from t_me30 where mertido ~ '^2003-12-([0-9]{2})
([0-9]{2}):(15|30)';

All dates whose minutes = 15 or 30 and whose year is:

select * from t_me30 where mertido ~ '^2003--([0-9]{2})-([0-9]{2})
([0-9]{2}):(15|30)';

All dates whose minutes = 15 or 30

select * from t_me30 where mertido ~
'^([0-9]{1,4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):(15|30)';

Note I have made the assumption that the year can be anything from 1 AD
to now, hence the {0,4} part.

HTH


Nick


P.S. I am more familiar with Perl Regular Expressions, and not POSIX
ones, so this may not be the most concise form.