Kevin Hamilton wrote:

>I'm curious, would the following have the same result as using STARTING
>WITH? Because this is how I probably would have written it:
>
>select p.name, d.DateOfDiagnosis
>from patient p
>join diagnosis d on d.patientid=p.patientid where d.diagnosis
>like '206%'
>
>
>

Kevin,

Yes, in non pre-prepared statements LIKE 'something%' is internally
converted to STARTING WITH 'something'

If you use prepared queries like this

select * from Customers where Name like :Name

then this is not internaly converted to STARTING WITH since the
parameter could be something like "_lexandre".
There is no guarantee to the engine that a prepared statement with LIKE
could be converted to STARTING WITH

STARTING WITH could use an index if it's available, LIKE could not.

The case where LIKe use an index are for non pre-prepared queries that
uses like and not starts with a wildcard.

A query like this
Select * from customer where Name like 'Ale%'
are internally converted to
Select * from customer where Name Starting with 'Ale' and Name like 'Ale%'

Select * from customer where Name like 'Ale_an%'
are internally converted to
Select * from customer where Name Starting with 'Ale' and Name like
'Ale_an%'

The converted query could use an index if it is available.

see you !



see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.6/33 - Release Date: 28/06/2005