Quotation character problem in SQL command

yokzu

Hi,

I want to search a string(with special characters) in DB but it has quotation character. I tryed lot of things but it didnt work.
String is; '':;'xx'

How may search this string with a select command?

Select * FROM table where String=''

Regards.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted answer
Perhaps, having doubled the embedded quotes, it is just a conversion problem. I'd try:

Select * FROM table where CAST(String AS varchar) = ''':;''xx'
0
Sam Hobbs14y ago
Would a parameterized query solve the problem? If so then that should have been suggested. I know that many experienced database programmser say that we need to always use parameterized queries.
0
yokzu14y ago
Thanks Guys for your helps.
It solved my problem Vulpes:)
0
Chintan Rathod14y ago
try to implement this one... Select * FROM table where String='":;''xx''' (single quote)(double quote):;(single quote)(single quote)xx(single quote)(single quote)(single quote)
0
yokzu14y ago
Error is ; The data types text and varchar are incompatible in the equal to operator.
0
yokzu14y ago
Thanks for your reply Chintan. But I try this method, so I doubled the quote characters but it still didnt work. My query is like this when I doubled quotation but its getting error.

Select * FROM table where String=''''':;''xx'''

The string which I wanna search is ; '':;'xx'
0
Chintan Rathod14y ago
When the SQL statement is assembled via string concatenation, the appearance of a single quote inside the QueryString value will break the statement. For example, when built, the statement will look like this:

SELECT * FROM TableName WHERE FieldName = 'QueryString's Value'

This is broken as the value closing delimiter appears twice; that is, there are an odd number of single quotemarks.

Solution

For most DBMS, doubling the single-quote character is the default means of escaping a single-quote.

"SELECT * FROM TableName WHERE FieldName = '" +
replace(Request.QueryString("ProNumber"), "'", "''") + "'"

This fix will produce the following statement: "SELECT * FROM TableName WHERE FieldName = 'QueryString''s Value'"
0