Oracle REGEX functions

Question

I am working on Oracle 10gR2.

I am working on a column which stores username. Let's say that one of the values in this column is "Ankur". I want to fetch all records where username is a concatenated string of "Ankur" followed by some numerical digits, like "Ankur1", "Ankur2", "Ankur345" and so on. I do not want to get records with values such as "Ankurab1" - that is anything which is concatenation of some characters to my input string.

I tried to use REGEX functions to achieve the desired result, but am not able to.

I was trying:

SELECT 1 FROM dual WHERE regexp_like ('Ankur123', '^Ankur[:digit:]$');

Can anyone help me here?

I was trying REGEX_LIKE SELECT 1 FROM dual WHERE regexp_like ('Ankur123', '^Ankur[:digit:]$'); — Incognito
– Incognito, Commented Sep 30, 2011 at 7:00
This does not work, as I believe REGEX_ functions only expect wild card characters and patterns for matching and evaluating expressions. I am clueless as to how I can pass on a defined value and then specify wild card characters for a match — Incognito
– Incognito, Commented Sep 30, 2011 at 7:01

Tim Pietzcker · Accepted Answer · 2011-09-30 07:00:11Z

7

Oracle uses POSIX EREs (which don't support the common \d shorthand), so you can use

^Ankur[0-9]+$

Your version would nearly have worked, too:

^Ankur[[:digit:]]+$

One set of [...] for the character class, and one for the [:digit:] subset. And of course a + to allow more than just one digit.

answered Sep 30, 2011 at 7:00

Tim Pietzcker

337k59 gold badges521 silver badges572 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

René Nyffenegger · Accepted Answer · 2011-09-30 07:02:18Z

3

You were close

Try regexp_like ('Ankur123', '^Ankur[[:digit:]]+$') instead.

Note the [[ instead of the single [ and the + in front of the $.

Without the + the regexp only matches for exactly one digit after the String Ankur.

answered Sep 30, 2011 at 7:02

René Nyffenegger

40.9k36 gold badges180 silver badges321 bronze badges

Collectives™ on Stack Overflow

Oracle REGEX functions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related