Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

It seems easy but I'm missing something. I have a MySQL database that is connected from access through ODBC connector.

I'm looking for the fields where there are # symbol but I'm getting zero results, although I have a field with # (Access query).

Data

id    obs
1     #rejected

Access Query where I do not have any results

SELECT id, obs
FROM Person
WHERE obs Like '*#*'

+info added from answers

MySQL Query works fine

SELECT id, obs
FROM Person
WHERE obs Like '%#%'

SELECT id, obs
FROM Person
WHERE obs LOCATE('#',obs) >0

Which is the escape character in Access? Any ideas to fix Access query?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

For Access you can use:

WHERE obs LIKE '*[#]*'

There are three wildcard characters in Access that can be used with LIKE operator:

`*` for a string of arbitrary length (equivalent ANSI: `%`)
`?` for a single character (equivalent ANSI: `_`)
`#` for a single numeric digit 
`[` for escaping

If you want to search for one of those wildcards, you have to enclose them in brackets: [ ]

You can find more info in the msdn article: Separate But Equal Wildcard Characters in SQL Server and Access

share|improve this answer

Just replace the '*' with '%' as

SELECT id, obs
FROM Person
WHERE obs Like '%#%'
share|improve this answer

To only check the occurrence of '#' you can use LOCATE()

SELECT id, obs
FROM Person
 WHERE LOCATE('#',obs) >0

Fiddle Demo

For you access query

SELECT id, obs
FROM Person
 WHERE obs Like '*[#]*'
share|improve this answer
    
You can remove the fiddle demos. SQL-Fiddle does not support Access. –  ypercube Apr 3 at 13:09
    
@ypercube thanks i have updated my answer and removed fiddle for second query i have posted solution for mysql but my answer was edited by someone for access query –  M Khalid Junaid Apr 3 at 13:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.