[thelist] Knuckle-headed SQL Question

[rudy]

> Here's my solution for ASP/Access database... I'd be interested to know 
if
> there is a better option .. this one works just fine for me tho, but am 
not
> sure how well it does performance wise.
>
> SqlString = SqlString & "SELECT whatever FROM wherever "
> SqlString = SqlString & "WHERE (Name LIKE '0%') OR (Name LIKE '1%') OR 
(Name
> LIKE '2%') OR (Name LIKE '3%') OR (Name LIKE '4%') OR (Name LIKE '5%') 
OR
> (Name LIKE '6%') OR (Name LIKE '7%') OR (Name LIKE '8%') OR (Name LIKE 
'9%')
> "

hi michele

a better option?  that depends on the database (access? mysql? sql/server? 
oracle? db2? informix?)

it also depends on whether there are any indexes declared on the column in 
question -- no index, you get a scan of all the rows in the table

note that using LIKE in a query causes (used to cause? i'm a bit out of 
touch with the latest database developments) some database optimizers to 
throw up their hands, figuratively speaking, and scan all the rows of the 
table regardless of whether an index exists

it depends on how sophisticated the optimizer is in terms of calculating 
the potential number of input/output operations using the index to access 
the table rows, versus the number of i/o's just to scan all the table rows

for a query such as LIKE 'fumblegoozer%' in a 15-character column, using 
the index might be efficient

for a query such as LIKE '%foo' (in any size column), the index would be 
pretty useless

anyhow, michele, for the example you gave, with ten LIKEs all OR'd 
together, just the fact that there are so many ORs might throw some 
optimizers off

you might want to try

   where Name >= '0'

instead, but be aware that you'll get everything that starts not only with 
a digit but also any other characters that collate higher than the 9 
(although off the top of my head i can't think of any)

furthermore, some optimizers will evaluate Name >= '0' favourably and will 
use the index after all

finally, i really like james' idea of using REGEXP, but of course the 
particular database language has to support that

rudy
r937.com