Solved

query number sign (#) in  sql

Posted on 2011-09-25
4
488 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 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