Home » SQL & PL/SQL » SQL & PL/SQL » search by like operator (oracle9i and developersuite9i)
search by like operator [message #400882] Thu, 30 April 2009 04:24 Go to next message
singhal10
Messages: 4
Registered: April 2009
Location: INDIA
Junior Member
Hi all
i have problum i want to search name starting with vik
it may be in upper case may be in lower case may be first character is capital may be first charactrer is small
i mean to say restriction free search
give some idea
Thanks And Regards
Vikas singhal
Re: search by like operator [message #400884 is a reply to message #400882] Thu, 30 April 2009 04:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
WHERE UPPER(name) LIKE 'VIK%'
Re: search by like operator [message #400887 is a reply to message #400884] Thu, 30 April 2009 04:36 Go to previous messageGo to next message
singhal10
Messages: 4
Registered: April 2009
Location: INDIA
Junior Member
i want all the name whether it was in lower or upper or initcap
how i will use the like or any other command for this

Thanks for REply
Re: search by like operator [message #400888 is a reply to message #400882] Thu, 30 April 2009 04:36 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use equal case on both sides, such as
where upper(name_column) like upper('vik%')
Re: search by like operator [message #400891 is a reply to message #400887] Thu, 30 April 2009 05:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In exactly the way I just showed you.
with src as (select 'Vikram' col_1 from dual union all
             select 'VIKRAM' col_1 from dual union all
             select 'vikram' col_1 from dual union all
             select 'ViKram' col_1 from dual)
select col_1
from   src
where  upper(col_1) like 'VIK%';
icon7.gif  Re: search by like operator [message #400892 is a reply to message #400882] Thu, 30 April 2009 05:12 Go to previous messageGo to next message
singhal10
Messages: 4
Registered: April 2009
Location: INDIA
Junior Member
Thanks
Re: search by like operator [message #400907 is a reply to message #400891] Thu, 30 April 2009 05:58 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
why to complicate using WITH and AS,

try with,

select col1 from table1 where upper(col1) like 'VIK%';
Re: search by like operator [message #400911 is a reply to message #400907] Thu, 30 April 2009 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what if you have no table named table1? Your query will throw an error.

You just don't understand the purpose of "with" part in JRowbottom's example. It does not complicate anything, it just shows a full working example.
See the difference:
SQL> select col1 from table1 where upper(col1) like 'VIK%'; 
select col1 from table1 where upper(col1) like 'VIK%'
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> with src as (select 'Vikram' col_1 from dual union all
  2               select 'VIKRAM' col_1 from dual union all
  3               select 'vikram' col_1 from dual union all
  4               select 'ViKram' col_1 from dual)
  5  select col_1
  6  from   src
  7  where  upper(col_1) like 'VIK%';
COL_1
------
Vikram
VIKRAM
vikram
ViKram

4 rows selected.

Regards
Michel

[Updated on: Thu, 30 April 2009 06:05]

Report message to a moderator

Re: search by like operator [message #400912 is a reply to message #400907] Thu, 30 April 2009 06:03 Go to previous message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think that you have no idea what complication with WITH and AS mean.
Previous Topic: SQL order by Issue
Next Topic: Getting more than 'anonymous block completed' from AUTONOMOUS_TRANSACTION
Goto Forum:
  


Current Time: Wed Dec 07 04:57:57 CST 2016

Total time taken to generate the page: 0.15587 seconds