Query on Regular Expressions in Oracle
I'm trying my hand in learning Regular Expressions in Oracle ( rather, my first attempt in doing anything with RegEx).
What does the character ^
signify at the start ?
The documentation mentions
Use the caret and dollar sign to define patterns that match the start or end of a string.
^ defines that start of a string or column 1 of the string.
So by using '^[*est]'
as the pattern, my understanding is that match anything which has -est as its ending
.
However, when I tried it out,
SQL> select 1 from dual where regexp_like('test','^[*est]');
1
----------
1
SQL> select 1 from dual where regexp_like('best','^[*est]');
no rows selected
SQL> select 1 from dual where regexp_like('fest','^[*est]');
no rows selected
Removing the ^
however, and we get
SQL> select 1 from dual where regexp_like('fest','[*est]');
1
----------
1
开发者_开发知识库
SQL> select 1 from dual where regexp_like('best','[*est]');
1
----------
1
SQL> select 1 from dual where regexp_like('test','^[*est]');
1
----------
1
Why is this so ? Why is it that in the first case, the match happens for `test' but not for others ?
select 1 from dual where regexp_like('best','^[*est]');
[]
in regexps means "any of the listed characters"
Inside []
, the asterisk loses its special meaning and means just the asterisk.
The regexp above matches any string that begins with *
, e
, s
or t
(any of the listed characters following the beginning of the string).
To select words ending on -est
, use this:
select 1 from dual where regexp_like('nest','est$')
which means "the string est
followed by the end of string ($
)"
Unless the regex syntax in Oracle is very different from all other regex implementations then you porbably want to change your []
to ()
. [est]
matches either "e", "s" or "t". (est)
on the other hand matches "est".
精彩评论