Inserting a String that contains a ' single quote

[Jhankins]

Need help inserting a field into a varchar that contains a single quote

INSERT INTO LocationExport
( Name)
VALUES ('SHOP 13 SCOTT' S ARCADE49 - 51 BEECROFT ROAD ')

see how its 'S is there a function that can fix this??

 

[SpiderBear6]

Just use 2 single quotes instead of the one and only one will be inserted.

 

[LogicalmanUS]

Jhankins,

Use: REPLACE ( myString, "'" , "''&quot

Where myString is the original string, "'" is the string to search for, and "''" is the replacement string

Below is a Sproc that shows how to use this, and the call from Query Analyzer:

-- EXEC usp_Replace "HERE'S THE CHOICES"

CREATE PROCEDURE [dbo].[usp_Replace]
@MYSTRING varchar(100)
AS
SET @MYSTRING = REPLACE(@MYSTRING, CHAR(39), CHAR(39) + CHAR(39))
PRINT @MYSTRING



Logicalman

 

[BlackburnKL]

What about 2 single quotes? An encryption routine returned a string with 2 single quotes together and the approach described above converted it to 4 single quotes. It didn't cause the process to throw an error, but it also didn't find the matching record in my SQL Server database. It could be a provider problem, but I'm hoping that someone else has trod this path before me and has a simple answer....

BlackburnKL

 

[maswien]

I think ThunderBear is right, two single quotes wil be OK
when you want insert one single quote, 4 single quotes for
inserting two sigle quotes. I can't see any problem here.

 

[BlackburnKL]

It doesn't work. Suppose that my user name is BlackburnKL and my encrypted password is ÒR1ª''>fŸT] in this simplified example. Using the described approach, I develop the following SQL string to retrieve my information from the database:

SELECT * FROM Users WHERE Name = 'BlackburnKL' AND Password = 'ÒR1ª''''>fŸT]'

From VB6 and even in native SQL Server, it doesn't find the record with this SQL statement. And I've tried 3 single quotes, too. It results in an error. The 4 don't, but no record is returned either.

Any ideas?

BlackburnKL

 

[SQLBill]

My understanding is that the password is encrypted within SQL and you CANNOT retrieve by password.

Try this as a test:

USE Master
SELECT password
FROM Syslogins
WHERE name = 'BlackburnKL'

Then you'll see what the password must look like to make a match.

-SQLBill

 

[BlackburnKL]

SQLBill,

Thanks for your response. Allow me to provide you with some follow-up information.

By the way, the password to which I am referring is an application password which is one of ours, not SQL Server's.

I committed an error testing this in SQL Server because I was attempting to find a record that I had not yet saved with the changes. It couldn't find the record because it wasn't there yet! OOPS!

The explanation given (replacing each single quote with two single quotes)--resulting in 4 consecutive single quotes in this case--works in native SQL Server.

Unfortunately, it still doesn't seem to work coming through the provider from VB6. It seems to be a string manipulation problem in VB. I got around it once by substituting the '''' with ''" & "'', but VB does some interesting things with strings manipulated like that. If I reference the string again (say to check for the occurence of another set of 2 single quotes, it converts it back to 4 consecutive single quotes (which doesn't work through the provider). I also ran into a strange thing (perhaps an "undocumented feature&quot where using a Replace(strSQL, "''''", "''"" & ""''&quot command in VB makes the desired change, but also converts a single space to a double space. Go figure.

I am going to try a workaround. Maybe this issue should have been raised in the VB forum instead....

BlackburnKL