regexp_like can do something like that
Milo Tan, April 29, 2009 - 7:14 pm UTC
The hard part is to come up with the correct regular expression patterns to use. I think regexp_like should be able to do it.
SQL> Create Table t
2 (name varchar2(100));
Table created.
SQL>
SQL> Insert Into t Values ('shashivinisky');
1 row created.
SQL>
SQL> Insert Into t Values ('millsasho');
1 row created.
SQL>
SQL> Insert Into t Values ('sasho');
1 row created.
SQL>
SQL> Insert Into t Values ('sashorags');
1 row created.
SQL>
SQL> Insert Into t Values ('rages');
1 row created.
SQL>
SQL> Insert Into t values ('rags');
1 row created.
SQL>
SQL> Insert Into t values ('ragshashi');
1 row created.
SQL>
SQL> Insert Into t values ('aaa');
1 row created.
SQL>
SQL> Insert Into t Values ('shaft');
1 row created.
SQL>
SQL> Insert Into t Values ('tense');
1 row created.
SQL> Commit;
Commit complete.
SQL> select * from t;
NAME
--------------------------------------------------------------------------------
shashivinisky
millsasho
sasho
sashorags
rages
rags
ragshashi
aaa
shaft
tense
10 rows selected.
SQL> Select *
2 From t
3 Where regexp_like(name,'^sasho$|^rags$|^shashi*');
NAME
--------------------------------------------------------------------------------
shashivinisky
sasho
rags
April 29, 2009 - 9:57 pm UTC
speed = not so good
not a fan of regular expressions UNLESS you cannot do it via "regular" stuff (all pun intended)
if you do this:
ops$tkyte%ORA10GR2> drop table t;
Table dropped.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t as select * from all_objects;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * From t Where regexp_like(object_name,'^sasho$|^rags$|^shashi*');
no rows selected
ops$tkyte%ORA10GR2> select * from t where object_name like 'sasho' or object_name like 'rags' or object_name like 'shashi%';
no rows selected
ops$tkyte%ORA10GR2> alter session set sql_trace=true;
Session altered.
ops$tkyte%ORA10GR2> select * From t Where regexp_like(object_name,'^sasho$|^rags$|^shashi*');
no rows selected
ops$tkyte%ORA10GR2> select * from t where object_name like 'sasho' or object_name like 'rags' or object_name like 'shashi%';
no rows selected
tkprof will show something like this:
select *
From
t Where regexp_like(object_name,'^sasho$|^rags$|^shashi*')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.67 0.66 0 690 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.67 0.66 0 691 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 516
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL T (cr=690 pr=0 pw=0 time=660370 us)
********************************************************************************
select *
from
t where object_name like 'sasho' or object_name like 'rags' or object_name
like 'shashi%'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.01 0 690 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 691 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 516
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL T (cr=690 pr=0 pw=0 time=14279 us)
or
Sokrates, April 30, 2009 - 3:54 am UTC
SELECT e.name
FROM employee e,
(
select
decode(level,
1, 'sasho',
2, 'shashi%',
3, 'rags') n
from dual
connect by level <=3
) l
WHERE e.name LIKE l.n
April 30, 2009 - 10:20 am UTC
between - non-equi joins can change the answer.
ops$tkyte%ORA10GR2> select ename
2 from scott.emp,
3 (select '%A%' n from dual union all
4 select '%L%' n from dual ) l
5 where emp.ename like l.n
6 /
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
CLARK
ADAMS
JAMES
ALLEN
BLAKE
CLARK
MILLER
11 rows selected.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select ename
2 from scott.emp
3 where emp.ename like '%A%' or emp.ename like '%L%'
4 /
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
CLARK
ADAMS
JAMES
MILLER
8 rows selected.
Neat solution
Nicosa, April 30, 2009 - 5:22 am UTC
@Sokrates :
Neat solution, I never thought of joining using LIKE operator.
Actually it is not exactly the same query if 2 values of inlist overlaps :
Using scott/tiger, if you :
SELECT e.ename
FROM emp e,
(
select
decode(level,
1, 'SMITH',
2, 'M%',
3, 'MA%',
4, 'KING') n
from dual
connect by level <=4
) l
WHERE e.ename LIKE l.n;
It will retrieve MARTIN record twice (one for 'M%', one for 'MA%').
So you might have to make sure there is no overlap with IN values, or add a DISTINCT to remove duplicate outputs.
April 30, 2009 - 10:21 am UTC
distinct would be wrong in general - depends on the question.
I personally did not suggest this because.... well - it sure isn't any easier than coding an OR
Mihail Bratu, April 30, 2009 - 5:25 am UTC
Hi Sokrates,
You need distinct as well!
Regards
April 30, 2009 - 10:22 am UTC
only if distinct applies to the question - ename is not unique in EMP, distinct would/could destroy the result set as much as the non-equi join does...
of course
Sokrates, May 04, 2009 - 9:04 am UTC
this one looks probably better
with l as
(
select
decode(level,
1, 'sasho',
2, 'shashi%',
3, 'rags') n
from dual
connect by level <=3
)
SELECT e.name
FROM employee e
where exists
(
select null
from l
where e.name like l.n
)
LIKE
Venki, May 22, 2012 - 1:34 am UTC
Hi Tom,
I have to select a single number out of a list of numbers.
For ex: Updated_by column contains values (12,20,21,14,13,10,9) or a combination of such values or even single value at times.
I need to check in my procedure whether it is updated_by 20 or 21 or 14 or 9 or 10 then do something.
As per your post above and my understanding, you suggest to better use 'OR'.
So my query should be something like ,
select * from some_table where NVL(updated_by,0) like '%20%' or NVL(updated_by,0)like '%21%' or NVL(updated_by,0) like '%14%' or NVL(updated_by,0) like '%9%' or NVL (updated_by,0) like '%10%';
This will satisfy all conditions ,even if my column contains one single value of say '9' .
Please correct me if I am wrong
May 22, 2012 - 8:15 am UTC
you would need to wrap commas around that - so that looking for 9 doesn't find 19 or 99
something like:
where (','||updated_by||',' like '%,20,%'
or ','||updated_by||',' like '%,9,%
or .... )
you do not need the NVL (do not want actually)
and promise never to implement such a "data model" again, comma delimited lists do not belong in relational databases.
Sokrates is AWESOME!
SheFixesThings, November 13, 2012 - 6:00 pm UTC
I know it's much later than this post was written but I needed to say THANK YOU to Sokrates!!! His AWESOME idea REALLY saved me a ton!!!
SELECT e.name
FROM employee e,
(
select
decode(level,
1, 'sasho',
2, 'shashi%',
3, 'rags') n
from dual
connect by level <=3
) l
WHERE e.name LIKE l.n
A reader, December 21, 2012 - 1:23 am UTC
LISTAGG and LIKE IN list
Duke Ganote, April 19, 2014 - 11:41 am UTC
If you've got 11gR2, it's easy to list your matching LIKE patterns:
WITH
pattern_list AS (
SELECT '%A%' pattern FROM DUAL UNION ALL
SELECT '%L%' pattern FROM DUAL
)
SELECT ename
, LISTAGG(pattern,',')WITHIN
GROUP(ORDER BY pattern) matches
FROM emp
JOIN pattern_list
ON emp.ename LIKE pattern
GROUP BY ename
ORDER BY 1
/
ENAME MATCHES
---------- ------------------------------
ADAMS %A%
ALLEN %A%,%L%
BLAKE %A%,%L%
CLARK %A%,%L%
JAMES %A%
MARTIN %A%
MILLER %L%
WARD %A%
8 rows selected.
Boring
Sam McKenney, February 17, 2016 - 12:47 am UTC
Anyone can answer Tom's questions with the team he has at his disposal. Let Tom answer his own.
A reader, April 26, 2018 - 4:09 pm UTC
You suck
A reader, April 09, 2019 - 10:46 am UTC
You suck
April 10, 2019 - 1:55 am UTC
How very constructive :-)
He is awesome
Vengata Guruswamy, April 21, 2021 - 10:42 pm UTC
Please carry your hate some where else to vent out.[There are many ]
People here are volunteers spending their personal time to contribute to educate and grow.
April 27, 2021 - 5:56 am UTC
Thanks for the support.
Rest assured, people venting is just water of a ducks back to us.