[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2009-03-31
7
Medium Priority
?
328 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
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

591 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