开发者

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".

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜