Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Robert.

Asked: October 27, 2008 - 4:30 pm UTC

Last updated: May 23, 2012 - 10:25 am UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked 

Tom,

In sql*plus we can do an IN...

select * from x
where animals IN ('DOG', 'CAT', 'RAT')
/

Or we can do a LIKE...

select * from x
where animals LIKE ('%OG')

But if we want BOTH we have to do this... 

select * from x
where animals LIKE ('%OG')
   or animals IN ('CAT', 'RAT')
/

But they SHOULD allow a construct like this...

select * from x
where animals IN ('DOG', '%AT')
/

OR

select * from x
where animals LIKE ('DOG', '%AT')
/


What do you think?
Or is there another easy way to acheive this LIKE/IN 'mashup'.

Thanks!

Robert.

and Tom said...

first, sqlplus doesn't let you do this.

sqlplus is a simple command line interface - it accepts user typed input and does something about it. If you give it a select statement, sqlplus passes that off to the server and the server "does the sql". sqlplus is just a command line program you or I could have written - very simple tool.

Just so you do not get confused. sqlplus is NOT sql. sqlplus is a very tiny command line interface with limited scripting and reporting capabilities.


The feature you are talking about it SQL.



As for the desire to have "where x like ( <a set> )" - no, I don't agree. IN is a set operator, NOT IN is a set operator. LIKE is just a scalar operator. It compares expression1 to expression2 and evaluates to true if expression1 is "like" expression2.


Your 'mashup' would simply be:

where animals like 'DOG' or animals like '%AT'


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the correction. But...

Robert, October 28, 2008 - 4:34 pm UTC

Tom,

Correction well taken on SQL*Plus vs. SQL! :)

As for the other more philosophical response, that is a little over my head. I respect that you are no doubt technically correct from a strict theoretical point of view.

But there are certainly many accomodations within SQL which combine various operations to make the language more powerful?

How about a new operation such as LIKEIN OR INLIKE to accomplish my goal?

Thanks,

Robert.
Tom Kyte
October 28, 2008 - 7:07 pm UTC

Since there is already a well defined way to do it (OR'ing and an IN is just expanded to an OR) - it is not likely to generate too much attention

unless and until the ansi committee thought it was a good idea and folded it into the standard for the language.

of course, there is always this:

ops$tkyte%ORA10GR2> select *
  2    from all_users, table( sys.odcivarchar2list( 'SYS%', 'OPS$%' ) ) x
  3   where all_users.username like x.column_value
  4  /

USERNAME                          USER_ID CREATED   COLUMN_VALUE
------------------------------ ---------- --------- --------------------
OPS$ORA10GR2                           56 14-DEC-05 OPS$%
OPS$TKYTE                             435 27-OCT-08 OPS$%
SYSMAN                                 51 30-JUN-05 SYS%
SYS                                     0 30-JUN-05 SYS%
SYSTEM                                  5 30-JUN-05 SYS%


not that I'm recommending it (I'm not) and it does suffer from the "join" problem in that you probably need DISTINCT to avoid getting the same data twice...

ops$tkyte%ORA10GR2> select *
  2    from all_users, table( sys.odcivarchar2list( 'SYS%', 'S%' ) ) x
  3   where all_users.username like x.column_value
  4  /

USERNAME                          USER_ID CREATED   COLUMN_VALUE
------------------------------ ---------- --------- --------------------
SCOTT                                  84 12-FEB-06 S%
SI_INFORMTN_SCHEMA                     45 30-JUN-05 S%
SYSMAN                                 51 30-JUN-05 SYS%
SYSMAN                                 51 30-JUN-05 S%
SYS                                     0 30-JUN-05 SYS%
SYS                                     0 30-JUN-05 S%
SYSTEM                                  5 30-JUN-05 SYS%
SYSTEM                                  5 30-JUN-05 S%

8 rows selected.

REGEXP_LIKE

Gary, October 28, 2008 - 8:25 pm UTC

There's also REGEXP_LIKE. It's a LIKE, but the regular expressions incorporate OR logic with the pipe character.
Of course multiple predicates with an OR can provide the optimizer with additional options for using indexes.

select table_name
from user_tables
where regexp_like(table_name,'JO.*|REGIONS');

TABLE_NAME
------------------------------
REGIONS
JOBS
JOB_HISTORY

Thank you Gary

Robert, November 02, 2008 - 5:15 pm UTC

Gary,

Thanks a lot.

I think I can settle on that as a good solution and what I was really looking for.

Thanks a lot Tom and Gary,

Robert.

regular expression not working with sqlplus in oracle 9i

Nilesh Kumar, May 23, 2012 - 6:44 am UTC

Hi Tom,
I was looking for using regular expression in oracle 9i, but when i am using the function regex_like() in SQL-Plus i am getting error "ORA-00920: invalid relational operator", but the same statement is running in PL/SQL Developer. And our problem is that we are mostly using SQL-Plus in our organization.
So please suggest me, how to use regular expression in oracle 9i using sqlplus.
Tom Kyte
May 23, 2012 - 10:25 am UTC

Oracle 9i did not have regular expression support, it was a feature added in Oracle 10g.

http://docs.oracle.com/cd/B14117_01/server.101/b10750/chapter1.htm#sthref874

the same statements are not running in plsql developer in 9i, 9i didn't do those.

To Robert - Why you need this ?

A reader, May 24, 2012 - 8:06 am UTC

"How about a new operation such as LIKEIN OR INLIKE to accomplish my goal?

"

You can simply do this with an OR clause as Tom mentioned. Why complicate things ?

animal like 'DOG%' OR animal like 'CAT%'.

Do you just want to save on typing ?



control characters from text and special characters and exceptions

Imran, February 14, 2019 - 12:20 pm UTC

Hello Chris,

I am still having problems in finding the correct query in Oracle Output that can show me in a particular column ex. 3 columns in a table like first_name, last_name and full_name:

first_name last_name full_name
-------------------------------

1.John Smith John Smith
2.João Sm!?th João Sm!?th
3.João Smith João Smith
4.John Smíth John Smíth
5.J?hn Sm|ith J?hn Sm|th

-----------------------------------------
Output: Should be:
1.João Sm!?th João Sm!?th
2.J?hn Sm|ith J?hn Sm|th


Permitted Characters (Below):
-----------------------------
ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜ'
ACEIOUAEIOUAEIOUAOEU, A-Z, a-z, 0-9


Should NOT be included such characters(Below):
----------------------------------------------
Restricted Like
! Like
" Like
# Like
& Like
( Like
) Like
, Like
: Like
; Like
? Like
[ Like
] Like
` Like
| Like


I saw your post here:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526745900346594796
but couldn´t still find out the solution.
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.