Home » SQL & PL/SQL » SQL & PL/SQL » like function
like function [message #232763] Mon, 23 April 2007 04:03 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi,

I am using oracle 9i. lets say I have one table t1 over name column. Now I want to search all the names in this table which have at least one Caps letter .

Seleact  name from T1,

Yash
yash
yAsh
yyyy
YASH

in need output like this

Yash
yAsh
YASH

I used query like this …
Select name from T1 where name like ‘%A%’ or name like ‘%B%’…....
..but that is not good way some one suggest some thing else..

--Yash



Re: like function [message #232767 is a reply to message #232763] Mon, 23 April 2007 04:06 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Why don't you just check whether the lower(text) is different from the text? If it is the same, that means all characters are lower case. The LOWER function can be found in the manuals.

SQL> WITH yourtable AS
  2   (
  3     SELECT 'Yash' thetext FROM dual UNION ALL
  4     SELECT 'yash' thetext FROM dual UNION ALL
  5     SELECT 'yAsh' thetext FROM dual UNION ALL
  6     SELECT 'yyyy' thetext FROM dual UNION ALL
  7     SELECT 'YASH' thetext FROM dual
  8   )
  9  SELECT *
 10  FROM yourtable
 11  WHERE LOWER(thetext) != thetext
 12  /

THET
----
Yash
yAsh
YASH


MHE
Re: like function [message #232771 is a reply to message #232767] Mon, 23 April 2007 04:18 Go to previous message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx Mehher !! i am not able to think like that in oracle....

that is the way to use oracle feature ....which i am expecting the expert...


--Yash
Previous Topic: Recent Joining records
Next Topic: Clarification on 'N' th High/Low salary
Goto Forum:
  


Current Time: Mon Dec 05 21:16:52 CST 2016

Total time taken to generate the page: 0.10489 seconds