Home » SQL & PL/SQL » SQL & PL/SQL » find names form the table
find names form the table [message #355201] Thu, 23 October 2008 02:04 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
i have a table emp with a column name

i want to write a query that will give me name starting with la,lb,lc.... till ls.

names starting with lt or later wont come.


how to write a query for that?

find names from table [message #355202 is a reply to message #355201] Thu, 23 October 2008 02:06 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
i have a table emp with a column name

i want to write a query that will give me name starting with la,lb,lc.... till ls.

names starting with lt or later wont come.


how to write a query for that?

Re: find names from table [message #355203 is a reply to message #355201] Thu, 23 October 2008 02:16 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

coumn_name like 'l%' and 
           Not like 'lt%' and
           Not like 'lu%' ....

Or

SUBSTR(coumn_name,1,1) ='l' and 
SUBSTR(coumn_name,2,1)NOT in ('t','u','v','w','x','y','z')


Excuse me for performance impact

Smile
Rajuvan.

[Updated on: Thu, 23 October 2008 02:19]

Report message to a moderator

icon6.gif  Re: find names from table [message #355204 is a reply to message #355202] Thu, 23 October 2008 02:18 Go to previous messageGo to next message
deepshikhahcl
Messages: 14
Registered: October 2008
Junior Member
Hi ,

You can use the below query.

select column_name from table_name where ASCII(substr(column_name,2,2)) BETWEEN 97 AND 115 ;


please let me know if it works.

[Updated on: Thu, 23 October 2008 02:22]

Report message to a moderator

Re: find names from table [message #355206 is a reply to message #355204] Thu, 23 October 2008 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why using ASCII function or code points?

Regards
Michel
Re: find names from table [message #355207 is a reply to message #355204] Thu, 23 October 2008 02:36 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
its working fine.thanks a lot.

but without using ascii can it be done?
Re: find names from table [message #355208 is a reply to message #355203] Thu, 23 October 2008 02:39 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

rajavu1 wrote on Thu, 23 October 2008 12:46
coumn_name like 'l%' and 
           Not like 'lt%' and
           Not like 'lu%' ....

Or

SUBSTR(coumn_name,1,1) ='l' and 
SUBSTR(coumn_name,2,1)NOT in ('t','u','v','w','x','y','z')


Excuse me for performance impact





Smile
Rajuvan.
Re: find names from table [message #355210 is a reply to message #355207] Thu, 23 October 2008 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
swas_fly wrote on Thu, 23 October 2008 09:36
its working fine.thanks a lot.

but without using ascii can it be done?

substr(column_name,2,2) between 'a' and 's'

Regards
Michel
Re: find names form the table [message #355214 is a reply to message #355201] Thu, 23 October 2008 02:50 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Rather

substr(column_name,2,1) between 'a' and 's'

* third parameter is 1

Smile
Rajuvan.
Re: find names form the table [message #355217 is a reply to message #355214] Thu, 23 October 2008 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sure, I just copied and pasted without thinking. Wink

Regards
Michel
Re: find names form the table [message #355220 is a reply to message #355217] Thu, 23 October 2008 03:14 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
many thanks for all the sugesstions
Re: find names form the table [message #355224 is a reply to message #355201] Thu, 23 October 2008 03:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
For a 10g Regxt solution You could try:
where  regexp_like(column_name,'l[a-s]') ;
Re: find names form the table [message #355241 is a reply to message #355224] Thu, 23 October 2008 03:54 Go to previous message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
this is fantastic.thanks
Previous Topic: looking for records with newest dates (merged)
Next Topic: How can I insert into Oracle DB not standard character like ''Ç,Ü" ?
Goto Forum:
  


Current Time: Sun Dec 11 00:06:56 CST 2016

Total time taken to generate the page: 0.07237 seconds