Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL - need to search for data/name with single quote

Posted on 2009-03-31
7
Medium Priority
?
324 Views
Last Modified: 2012-05-06
Simple query statement that I am stumbling with...  I have a dealerName column that has records with single quotes as data  

eg.  Vann's

The form bombs without any string cleaning to deal with the single quote.  

I created this to deal with it... but it still returns 'no records found'
function cleanQuote(searchTerm)
	dim preQuote
	dim postQute
	postQuote  = replace(searchTerm,"'","'")
	cleanQuote = postQuote
end function

Open in new window

0
Comment
Question by:jweissdandm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 23

Accepted Solution

by:
apresto earned 420 total points
ID: 24029561
Convert to two single quotes when you save and when you query:
postQuote  = replace(searchTerm,"'","''")
0
 
LVL 4

Expert Comment

by:amazingwolf1
ID: 24029583
try to search like this:
select * from MyTable where ColumnName = 'ddd''d'

Note that to search for a string with a single quote in MSSQL you should double the single quote ('').

HTH

0
 
LVL 2

Author Comment

by:jweissdandm
ID: 24029707
apresto...

Isn't my code doing the same thing?  If the columnData has a single quote, how does it return the rows if the columnData is replace with 2 single quotes?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 4

Assisted Solution

by:amazingwolf1
amazingwolf1 earned 400 total points
ID: 24029761
in sql server, the T-SQL syntax uses single quote as part of the syntax. to allow you to still use single quotes, the syntax requires to mark a single quote inside a column as a double single quote.

So your replace should be:
replace(searchTerm,"'","''")


0
 
LVL 2

Author Closing Comment

by:jweissdandm
ID: 31564853
Thanks gentlePeople
0
 
LVL 23

Expert Comment

by:apresto
ID: 24030571
Thanks for explaining amazingwolf1:) i was a little busy
Glad we could help
Apresto
0
 
LVL 4

Expert Comment

by:amazingwolf1
ID: 24036715
Thank you, aspresto - hope we will ALL stay as busy as we can :-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

618 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