SQL: 'Select... like "*...*"' How to find a text that contains "*"?

  • Thread starter Thread starter Bernd Muent
  • Start date Start date
B

Bernd Muent

Hi together,
I've got an Access Database with questions and answers for pupils, f.e.
"11*7" and "11+7".
Now I like to find all questions with multiplication sign "*",
especially "11*7".
I started with:
select question from tasks where question like "*11*7*",
but it finds "11*7" and "11+7". As expected.

I tried with a double "**":
select question from tasks where question like "*11**7*"
The same result.

Next idea with chr(42):
select question from tasks where question like "*11"&chr(42)&"7*"
Still the same result

Workaround:
select question from tasks where (question like "*11*7*") and
(instr(question,'*')>0)
Works fine. It finds only "11*7".
But it is not really ok, because it would also find:
11+7*3

Has anybody a better idea?

Thank you for tips, Bernd
 
Bernd Muent said:
Hi together,
I've got an Access Database with questions and answers for pupils, f.e.
"11*7" and "11+7".
Now I like to find all questions with multiplication sign "*",
especially "11*7".
I started with:
select question from tasks where question like "*11*7*",
but it finds "11*7" and "11+7". As expected.

I tried with a double "**":
select question from tasks where question like "*11**7*"
The same result.

Next idea with chr(42):
select question from tasks where question like "*11"&chr(42)&"7*"
Still the same result

Workaround:
select question from tasks where (question like "*11*7*") and
(instr(question,'*')>0)
Works fine. It finds only "11*7".
But it is not really ok, because it would also find:
11+7*3

Has anybody a better idea?

Thank you for tips, Bernd
Click to expand...

From Access help under "Wildcards":

"When using wildcard characters to search for other wildcard characters such
as an asterisk (*), question mark (?), number sign (#), opening bracket ([),
or hyphen (-), you must enclose the item you're searching for in brackets.
If you're searching for an exclamation point (!) or a closing bracket (]),
you don't need to enclose it in brackets.
For example, to search for a question mark, type [?] in the Find dialog box.
If you're searching for a hyphen and other characters simultaneously, place
the hyphen before or after all the other characters inside the brackets.
(However, if you have an exclamation point (!) after the opening bracket,
place the hyphen after the exclamation point.) "
 
Try something like this:

SELECT tblQuestions.Question
FROM tblQuestions
WHERE (((tblQuestions.Question) Like "***"))


Jamie
 
Back
Top