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
LVL 2
LJGAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Hi. I can query #'s just fine.

SELECT * FROM MyTable WHERE Company LIKE 'Joe''s #%';

Open in new window

0
 
LJGAuthor Commented:
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*'
0
 
LJGAuthor Commented:

This works in Access
SELECT * FROM MyTable WHERE Company Like 'Joe's [#]123*'
0
 
Kevin CrossChief Technology OfficerCommented:
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*".
0
All Courses

From novice to tech pro — start learning today.