asked on

query number sign (#) in sql

I have a new customer that I'm building an application for that has data with --># in it.

I need to query on the field and need to understand how to do it.

If I'm trying to lookup data --> Joe's #123

This works
SELECT * FROM MyTable WHERE Company Like 'Joe''s%'

This does not work (Incorrect syntax near '#'):
SELECT * FROM MyTable WHERE Company Like 'Joe''s '#'%'

I understand the correct answer is not to use reserved characters, but it's there now and I might work with them in the future to remove it, but...

Using SQL Server 2008

Thanks in advance for your help.
LJG

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LJG

ASKER

mwvisa1
Sorry - you are right
SELECT * FROM MyTable WHERE Company LIKE 'Joe''s #%';
works

I typed it in SMS wrong. My real problem is that it doesn't work in MS Access against SQL Server. I head for a different area

In Access
This works
SELECT * FROM MyTable WHERE Company Like 'Joe's*'

This does not:
SELECT * FROM MyTable WHERE Company Like 'Joe's #123*'

LJG

ASKER

This works in Access
SELECT * FROM MyTable WHERE Company Like 'Joe's [#]123*'

Kevin Cross

Yes, # in Access is for dates. I have been out of the house all afternoon, so glad you found that. Also, note you can usually use double quotes for string literals in Access, so LIKE "Joe's [#]123*".