CODEWITHSUNDEEP
sqlsql-server-2005
user412814
user412814

Reputation: 21

Incorrect SQL Syntax - Unclosed quotation mark after the character string

Now i have string a = "Unclosed quotation mark after the character string '%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC'. Incorrect syntax near '%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC'." for filter

I'm using replace ("'","''") replace ("%","[%]") replace ("[","[[]")

and I have as a result for string strSQL = 

select * from studiologs 
where [Message] 
like '%Unclosed quotation mark after the character string ''%Bamboo.Widgets.RequestApproval.CollectTask   ORDER BY Date DESC''.  Incorrect syntax near ''%Bamboo.Widgets.RequestApproval.CollectTask   ORDER BY Date DESC''.%'

but result always is null please help me replace this string for filter

thanks all

Upvotes: 2

Views: 49712

Answers (2)

Tom 'Blue' Piddock
Tom 'Blue' Piddock

Reputation: 2210

Best thing I would do here is to transfer your SQL query into a procedure, that way the string you give it wont need filtering as the punctuation in the string will not affect the syntax of the query.

So something like this:

USE MYDATABASE
CREATE PROC GET_STUDIO_LOGS
@INPUT_STRING AS NVARCHAR(1024)
AS 
BEGIN
SELECT * FROM STUDIOLOGS WHERE [Message] LIKE '%' + @INPUT_STRING + '%'
END


EXEC GET_STUDIO_LOGS 'Unclosed quotation mark after the character string ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''. Incorrect syntax near ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''.'

If you use a program to submit the SQL then you can submit the string parameter as it is without any change in punctuation. Doing it natively in SQL you just add another ' (quotemark) to each quotemark that is meant to be part of the string.

If you are trying to escape the % marks you can set an escape character first:

SET ESCAPE '\';
SELECT '\%abc' FROM Table

Try that out, tell me how it goes.

Upvotes: 1

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55499

I think you have missed one more quotation mark at end of query -

select * from studiologs where [Message] like '%Unclosed quotation mark after the character string ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''%'

or remove that last quotation mark as well, if your string does not have '

select * from studiologs where [Message] like '%Unclosed quotation mark after the character string ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC%'

Depending on what you are searching exactly

Upvotes: 1

Related Questions