This is simple, but fun. I have studied the regular expression features in Oracle but haven’t really used them. I needed a list of all the tables in a particular schema that started with a certain string and ended with three or four numbers but there were a number of other tables with similar patterns in the same schema – backups, etc. I’ve done this before with LIKE and % and _. But I went to review the LIKE command in the manual and ran across REGEXP_LIKE and decided to try it. It worked pretty well. Easier to specify the same thing than with LIKE I think. I ended up with a condition that would work with two or more numbers so it wasn’t exactly 3 or 4 numbers but all the tables I was looking for and none of the ones I didn’t need fit the regular expression. I built a simple test case in my own test schema that mirrored the situation on our real system and here is the resulting query and its output:
select table_name from user_tables where REGEXP_LIKE (table_name, '^TEST_[0-9][0-9]*[0-9]$') order by table_name; TABLE_NAME ------------------------------ TEST_123 TEST_4567
I had to have the ^ at the beginning and the $ at the end to indicate that the regular expression matched the entire table name. [0-9] matches one digit and [0-9]* matches zero or more digits. The _ in this example is just the literal character _ and doesn’t match anything else.
Here is a zip of my test script and its log.
– Bobby
You are seeking the {m,n} regex feature. It will match a minimum of m items and a max of n items.
> create table test_123 (x number)
table TEST_123 created.
> create table test_1 (x number)
table TEST_1 created.
> create table test_12 (x number)
table TEST_12 created.
> create table test_1234 (x number)
table TEST_1234 created.
> select ut.table_name
from user_tables ut
where regexp_like(ut.table_name,'^TEST_[0-9]{1,3}$')
TABLE_NAME
------------------------------
TEST_123
TEST_1
TEST_12
As you can see, it matched tables with 1 to 3 digits after TEST_ but not four.
That’s very nice. I don’t think I’ve ever seen the {m,n} syntax before, or if I have I’ve forgotten it. Thank you for your helpful comment.
I have similar requirement but tables are either ending with BKUP ,BKP or BACKUP. any idea how to implement
This should work:
select table_name
from user_tables
where
REGEXP_LIKE (table_name, ‘^TEST_(BKP|BKUP|BACKUP)$’)
order by table_name;
I want to use it in forms.I want such syntax which will accept only characters(a-z) (A-Z) and spaces in text field.and another syntax which will accept only number.Could u please help me.
Would this work for you?
SQL> select ‘Matches’ from dual where
2 regexp_like(‘asdfasd sadf adsf’,’^[a-zA-Z ]+$’);
‘MATCHE
——-
Matches
SQL>
SQL> select ‘Matches’ from dual where
2 regexp_like(‘123′,’^[a-zA-Z ]+$’);
no rows selected
SQL>
SQL> select ‘Matches’ from dual where
2 regexp_like(‘123′,’^[0-9]+$’);
‘MATCHE
——-
Matches
SQL>
SQL> select ‘Matches’ from dual where
2 regexp_like(‘123.34′,’^[0-9]+$’);
no rows selected
SQL>
SQL> select ‘Matches’ from dual where
2 regexp_like(‘123.34′,’^[0-9]+\.[0-9]+$’);
‘MATCHE
——-
Matches
You might also try googling “POSIX regular expressions” for more examples.
hi bobby,
can you help me for this question.
this is password validation.
my requirement is 1 upper case letter,1 lower case latter,1 number,1 symbol and my pass word size is min 8 characters length.
i worked as
select pass from passw where regexp_like(pass,’^[a-zA-Z0-9_@#&%]{1,8}$’,’i’);
but here some problem is there with satisfies the requirement also it will show based on length of characters.
please help me.
but here some problem is there without satisfying the requirement also it will show based on length of characters.
Suresh,
It might be helpful to break it up into multiple calls to regexp_like like this:
select pass from passw
where
regexp_like(pass,’^.*[A-Z].*$’) and
regexp_like(pass,’^.*[a-z].*$’) and
regexp_like(pass,’^.*[0-9].*$’) and
regexp_like(pass,’^.*[_@#&%].*$’) and
regexp_like(pass,’^.{8,}$’);
These calls check the following:
— 1 upper case letter
— 1 lower case letter
— 1 number
— 1 symbol
— password size is min 8 characters
Also, you don’t need the ‘i’ parameter because this makes the comparison case insensitive and you are checking for upper and lower case letters.
– Bobby
Holy smokes in 10 years the only valid answer on the whole freaking internet. Thank you big B!!!!!!
HI boby,
IN my table one column may contains data like this
variable=1
abc=abc
1=1
variable=variable
true=true
I want to retrieve columns which have same data before and after ‘=’
example. 1=1 and variable=variable.
The equal symbol ‘=’ is always there.
whatever data is there before ‘=’ same data should be there after ‘=’ .
I want to retrieve such data column.
Thank you for your comment. I am not sure that I understand your question. It sounds like you are talking about a SQL query with a condition in the where clause that has the same thing on both sides of the equal sign. For example:
select * from dual where 1=1;
In most cases a query will ignore any conditions in the where clause that have the same thing on both sides of the equal sign. So the example query will be treated as if it were really:
select * from dual;
But, I may not really understand what you are asking.
Bobby
select case when regexp_like(‘variable=variable’,'(^.*)=\1$’) then ‘T’ else ‘F’ end as out from dual