Solved

query number sign (#) in  sql

Posted on 2011-09-25
4
485 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:LJG
  • 2
  • 2
4 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36595337
Hi. I can query #'s just fine.

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

Open in new window

0
 
LVL 2

Author Comment

by:LJG
ID: 36595376
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
 
LVL 2

Author Comment

by:LJG
ID: 36595394

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

Expert Comment

by:Kevin Cross
ID: 36596132
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now