0

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?

2

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

1

Just replace the '*' with '%' as

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

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 '*[#]*'
  • You can remove the fiddle demos. SQL-Fiddle does not support Access. – ypercubeᵀᴹ Apr 3 '14 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 '14 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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