Oracle - Regular expressions

Asked

Viewed 39 times

0

Dear, I need to find out which users have the login pattern "E012345", that is, it begins with the letter E and proceeds with a string of numbers.

I tried the query below on the did not roll:

SELECT *
FROM tbl_usuarios
WHERE  REGEXP_LIKE  (usr_login, 'E%^n$');

Somebody please help me?

regex oracle11g

  • It wasn’t very clear but maybe: '^E[:digit:]{6}$', letter 'E' followed by 6 digits.

2 answers

1


You can use the expression [A-Za-z]{1}[0-9]{1,} for any letter followed by a sequence of numbers of 1 or more digits.

You can use the expression [Ee][0-9]{1,} for any occurrence beginning with the letter "E" or "e" followed by a sequence of numbers with 1 or more digits.

If the system is set to CASE INSENSITIVE you can ignore the uppercase letters in the expression.

  • 1

    Just one detail, {1} is redundant and can be removed - [A-Za-z]{1} is the same as [A-Za-z] (in fact, in general, (qualquer coisa){1} is the same as (qualquer coisa). And {1,} is equivalent to +, then it could be [Ee][0-9]+ - or [Ee]\d+

  • Perfect. You’re right. Is that I give priority to explicit writing. Thank you

0

Luciano B. M. Thanks, it worked out!

The complete query looks like this:

SELECT *
FROM tbl_usuarios
WHERE  REGEXP_LIKE    (usr_login, '[Ee][0-9]{1,}')

  • You can also use [Ee][0-9]+ for the + is equivalent to {1,}: https://docs.oracle.com/cd/B19306_01/B14251_01/adfns_regexp.htm#r3c1-T7 - and could also be [Ee]\d+ (the \d is a shortcut equivalent to [0-9])

Browser other questions tagged regex oracle11g

You are not signed in. Login or sign up in order to post.