Home » SQL & PL/SQL » SQL & PL/SQL » Like Pattern search
icon8.gif  Like Pattern search [message #263258] Wed, 29 August 2007 09:22 Go to next message
nageswara
Messages: 9
Registered: August 2007
Junior Member
Hi

Can any one please help me to execute the following

select * from companies where cname like '%AT&T%';

The above query asks for the value of T even if i give the escape charecter it wont work. Please suggest


Thanks in advance
Nageswara
Re: Like Pattern search [message #263259 is a reply to message #263258] Wed, 29 August 2007 09:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set define off

Regards
Michel
Re: Like Pattern search [message #263261 is a reply to message #263258] Wed, 29 August 2007 09:25 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
FAQ

By
Vamsi
Re: Like Pattern search [message #263276 is a reply to message #263258] Wed, 29 August 2007 10:21 Go to previous messageGo to next message
nageswara
Messages: 9
Registered: August 2007
Junior Member
Thanks a lot for the help.

I am facing another problem with like. Why is the below query not working as expected?

SQL> select ename from emp_21178 where ename like '%[A-Z]%';

no rows selected

SQL> select ename from emp_21178 where rownum<2;

ENAME
----------
SMITH

I am using Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

Is it a problem with the oracle version or am i doing somthing wrong?

Thanks
Nageswara
Re: Like Pattern search [message #263278 is a reply to message #263276] Wed, 29 August 2007 10:26 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Regular expressions are only available in Oracle 10g and above, and even there you can't usen them in LIKE like that, you have to use REGEXP_LIKE.

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html
Re: Like Pattern search [message #263290 is a reply to message #263258] Wed, 29 August 2007 10:57 Go to previous messageGo to next message
nageswara
Messages: 9
Registered: August 2007
Junior Member
How should I check if the data present in the column is not having any capital alphabet in Oracle 9i?

Is there any inbuilt function to do the job?
Re: Like Pattern search [message #263293 is a reply to message #263290] Wed, 29 August 2007 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No regexp, so no built-in to directly do this but you can do it with TRANSLATE.

Regards
Michel
Re: Like Pattern search [message #263325 is a reply to message #263258] Wed, 29 August 2007 13:30 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
How about

ename = LOWER(ename) -- No CAPS
ename <> LOWER(ename) -- CAPS
ename = UPPER(ename) -- CAPS only

(Assuming no NULL values)
Re: Like Pattern search [message #263333 is a reply to message #263325] Wed, 29 August 2007 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select case when data = lower(data) then 'NO CAPS' end nocaps,
  2         case when data <> lower(data) then 'CAPS' end caps,
  3         case when data = upper(data) then 'CAPS ONLY' end capsonly
  4  from (select 1 data from dual)
  5  /
NOCAPS  CAPS CAPSONLY
------- ---- ---------
NO CAPS      CAPS ONLY

1 row selected.

Regards
Michel
Re: Like Pattern search [message #263358 is a reply to message #263258] Wed, 29 August 2007 17:42 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
I guess I should have been more explicit.

First, the incorrect syntax suggested looking for capital letters
Quote:
'%[A-Z]%'

However the direct question suggests otherwise
Quote:
How should I check if the data present in the column is not having any capital alphabet in Oracle 9i?

So I provided a variety of ideas to be considered. Not necessarily the direct solution, but if you insist.
SELECT NVL(col, '*NULL*') FROM t;

COL
----------
UPPER
lower
Mixed
1
*NULL*

If capitals are wanted:
SELECT NVL(col, '*NVL*') col FROM t
WHERE col <> LOWER(col);

COL
----------
UPPER
Mixed

If capitals are wanted(not including NULL):
SELECT NVL(col, '*NVL*') col FROM t
WHERE col <> LOWER(col);

COL
----------
UPPER
Mixed


If capitals are not wanted (to include NULL):
SELECT NVL(col, '*NVL*') col FROM t
WHERE NOT NVL(col, '*') <> LOWER(NVL(col, '*'));

COL
----------
lower
1
*NVL*
icon10.gif  Re: Like Pattern search [message #263659 is a reply to message #263258] Thu, 30 August 2007 12:38 Go to previous messageGo to next message
nageswara
Messages: 9
Registered: August 2007
Junior Member
Hi All,

Thank you so much for all the innovative solutions

Regards,
Nageswara
Re: Like Pattern search [message #263970 is a reply to message #263258] Fri, 31 August 2007 06:56 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

SQL> select * 
     from   companies 
     where cname like '%AT/&T%' escape '/';


try this

NATESH

[mod-ed]Applied code tags - do so yourself next time.

[Updated on: Fri, 31 August 2007 07:05] by Moderator

Report message to a moderator

Re: Like Pattern search [message #263979 is a reply to message #263970] Fri, 31 August 2007 07:20 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
It won't help.

Haven't you seen the OP?
Quote:
The above query asks for the value of T even if i give the escape charecter it wont work. Please suggest
By
Vamsi
Re: Like Pattern search [message #263982 is a reply to message #263258] Fri, 31 August 2007 07:27 Go to previous messageGo to next message
lenin_babu55
Messages: 12
Registered: August 2007
Junior Member
hi

use concatination(||) symbol
select * from companies where cname like '%AT||&||T%';
Re: Like Pattern search [message #263983 is a reply to message #263982] Fri, 31 August 2007 07:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
lenin_babu55 wrote on Fri, 31 August 2007 14:27
hi

use concatination(||) symbol
select * from companies where cname like '%AT||&||T%';


Please show us a copy-paste of you doing this in sqlplus.
Oh, and selecting the value afterwards..

[Updated on: Fri, 31 August 2007 07:29]

Report message to a moderator

Re: Like Pattern search [message #263990 is a reply to message #263258] Fri, 31 August 2007 07:37 Go to previous messageGo to next message
lenin_babu55
Messages: 12
Registered: August 2007
Junior Member
or

like '%AT%&%T%';
Re: Like Pattern search [message #263995 is a reply to message #263990] Fri, 31 August 2007 07:51 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, this will return the row, but also has the potential to return a slew of other rows.
FOO SCOTT>l
  1* select * from test where TEST2 like '%AT%&%T%'
FOO SCOTT>/

     TEST1 TEST2
---------- ----------------------------------------
         1 AT&T
         1 ATTICA & DOG DAY AFTERNOON

[Updated on: Fri, 31 August 2007 07:52]

Report message to a moderator

Previous Topic: Character to number (merged)
Next Topic: Execute Immediate error
Goto Forum:
  


Current Time: Mon Dec 05 04:51:16 CST 2016

Total time taken to generate the page: 0.24911 seconds