By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,367 Members | 946 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,367 IT Pros & Developers. It's quick & easy.

simple SQL question

atsukoarai86
P: 6
I've got a small quandary here. Nothing big, just a little question.

I'm experimenting with SQL with a small database I'm similarly experimenting with, and I'm trying to write a select statement with the like clause and wild card patterns and all that.

Anyway, the code goes a' like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblContractors
  2. WHERE strLastName like 's*'
  3. ORDER BY strLastName asc; 
only when I click the execute button, it gives me a dialogue box that says "Enter Parameter Value" for "strLastName" ...

I'm doing this in Microsoft Access. I'm sure someone can explain this. Thank you in advance.
Sep 18 '08 #1
Share this Question
Share on Google+
2 Replies


docdiesel
Expert 100+
P: 297
Hi,

in SQL the wildcard parameter is the percent sign '%', so you should use a condition like "WHERE strLastName like 's%' ". But be aware that this comparison is case sensitive, means "Somename" won't match.

Furthermore statements like this use a lot of I/O and CPU because the SQL engine has to read and compare the value of each row. If you've got to compare to the first letter oftenly, you'd better add a separate column "FIRST_LETTER char(1)" and place an index on that.

Regards


Doc Diesel
Sep 19 '08 #2

atsukoarai86
P: 6
Hi,

in SQL the wildcard parameter is the percent sign '%', so you should use a condition like "WHERE strLastName like 's%' ". But be aware that this comparison is case sensitive, means "Somename" won't match.

Furthermore statements like this use a lot of I/O and CPU because the SQL engine has to read and compare the value of each row. If you've got to compare to the first letter oftenly, you'd better add a separate column "FIRST_LETTER char(1)" and place an index on that.

Regards


Doc Diesel
I know in Oracle SQL the Wildcard symbol is %, but in Microsoft Access, the wild card is asterisk *... Using % in MS Access 2003 didn't work out very well... I tried:

Expand|Select|Wrap|Line Numbers
  1. SELECT strLastName, dteHIreDate
  2. FROM tblContractors
  3. WHERE strLastName like '%sher%'
  4. ORDER BY dteHireDate;
And it didn't return anything, even though I know for a fact in the strLastName column there's a value that matches that pattern, "Sherman". When I change the % symbols in the select statement to * it works fine.

I don't know what I did to it, but for some reason the query works now. <.< I've tried to reproduce the problem, but I can't. But it was asking me for a parameter value, and I just didn't know what that meant...I guess what that meant was "you typed something, somewhere in wrong" lol... >_<

Anyway thank you for your input. :)
Sep 19 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.