Home » SQL & PL/SQL » SQL & PL/SQL » Confusion in SQL Query
Confusion in SQL Query [message #198292] Mon, 16 October 2006 08:19 Go to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

hi guys...

i am a bit confused in the following querry as it can be done in 2 ways

the querry is -->

Display the last name of all employees where the third letter of the name is an a.


now what i did was.....>

select last_name from employees
where substr(last_name,3,1) in ('a');


and the solution in the book says is this----->


SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';


which is the right way to this kind of querry??

thanks
Re: Confusion in SQL Query [message #198295 is a reply to message #198292] Mon, 16 October 2006 08:36 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Both of them work, although I would use
where substr(last_name,3,1) = 'a';

instead of
where substr(last_name,3,1) in ('a');

but that's just my preference.
Re: Confusion in SQL Query [message #198296 is a reply to message #198292] Mon, 16 October 2006 08:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Welcome to SQL.
As a general rule, there is always more than one way of doing anything in SQL - the trick is knowing which one to use.

I would favour the Substr approach personally, as it gives you the option of creating a function based index on Substr(last_name,3,1), and potentially avoiding a full table scan.

I'd have done
WHERE substr(last_name,3,1) = 'A'
rather than use IN, as it explicitly lets the optimiser know that it's looking for a single value.
Re: Confusion in SQL Query [message #198297 is a reply to message #198292] Mon, 16 October 2006 08:44 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

ok..thanks..

is there any way this topic can be closed now?? as i have got my answer...!?
Re: Confusion in SQL Query [message #198299 is a reply to message #198296] Mon, 16 October 2006 08:44 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
just go with this:

WHERE substr(upper(last_name),3,1) = 'A'
Re: Confusion in SQL Query [message #198300 is a reply to message #198297] Mon, 16 October 2006 08:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Consider it closed.
Previous Topic: Join two fields
Next Topic: POSTPAID TO PREPIAD TRAFFIC
Goto Forum:
  


Current Time: Fri Dec 02 14:22:11 CST 2016

Total time taken to generate the page: 0.10235 seconds