Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, Rahul.

Asked: March 20, 2019 - 12:57 pm UTC

Last updated: December 11, 2020 - 5:50 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Why is this statement returning value - 

select * from (
  SELECT 'AAaaanders4n' name
  FROM dual
)
WHERE REGEXP_LIKE (name, '^[A]{1}');


I have given {1} in regexp_like, still this statement returns 'AAaaanders4n'

and Chris said...

Because you're asking it to return rows where name has one uppercase A at the start. Which AAaaanders4n does.

What exactly are you trying to do here?

Rating

  (2 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Regexp_like Updates

Rahul K R, March 21, 2019 - 5:35 am UTC

I wanted to fetch only those which has only 1 A in the start.

For Ex:
select * from (SELECT 'AAaaaanders4n' name
FROM dual)
WHERE REGEXP_LIKE (name, 'a{1,3}');

This returns AAaaaanders4n where as it has more than 3a's. I assume {1,3} means it will search for pattern 'a' atleast once upto 3 times but I have 4 a's.

Am I missing something here?
Chris Saxon
March 21, 2019 - 9:34 am UTC

Then search for strings starting with A, followed by a character that's not A:

select * from (
  SELECT 'AAaaaanders4n' name
  FROM dual
  union all
  SELECT 'Alexander' name
  FROM dual
)
WHERE REGEXP_LIKE (name, '^A[^A]');

NAME        
Alexander

regexp_like dobuts

Sunny, December 11, 2020 - 9:49 am UTC

I have doubt how and when to use "[]" and "()".
can you show me easy way to understand or best example?
I refer oracle doc but not understand this.
Chris Saxon
December 11, 2020 - 5:50 pm UTC

The square brackets supply a set of characters to match. This could be a character class (such as :upper: - all uppercase letters) or a range of values (0-9 - the digits between zero and nine).

Regular parentheses mark a group. You have to have an exact match for the characters in the group.

So

[a-z] => any (lowercase) letter from a to z
(a-z) => match the exact string "a-z" (a hyphen z)

select * from (
  SELECT 'AAaaanders4n' name
  FROM dual
)
where regexp_like (name, '[a-z]{1}');

NAME           
AAaaanders4n  

select * from (
  SELECT 'AAaaanders4n' name
  FROM dual
)
where regexp_like (name, '(a-z){1}');

no rows selected

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.