Menu
OK, so I've got this line of code in a search stored procedure:
SET @where = 'job_code = ''' + REPLACE(@job_code, '''', '''''') + ''''
and there are basically two operations I'd like to streamline -the first being surrounding the concatenated value in single quotes. Obviously, in the above statement, I'm escaping a ' by using two '' and then ending the string with a ' so I can concatenate the actual value. There's got to be a better way!
The second of the operations would be the REPLACE(@job_code, '''', '''''') where I'm escaping any single quotes that might exist in the field.
Isn't there a much more elegant way of writing this line of code as a whole?
I thought it was the ESCAPE keyword but that's tied tightly to the LIKE statement, so no go there.
706
The simplest method to escape single quotes in SQL is to use two single quotes. For example, if you wanted to show the value O'Reilly, you would use two quotes in the middle instead of one. The single quote is the escape character in Oracle, SQL Server, MySQL, and PostgreSQL.
To run a dynamic SQL statement, run the stored procedure sp_executesql as shown below : EXEC sp_executesql N'SELECT statement'; Use prefix N with the sp_executesql to use dynamic SQL as a Unicode string.
What are the three ways that Dynamic SQL can be executed? Writing a query with parameters. Using EXEC. Using sp_executesql.
In ANSI SQL, the backslash character (\) is the escape character. To search for data that begins with the string \abc , the WHERE clause must use an escape character as follows: ... where col1 = '\\abc';
Not sure how you execute your sql query, if you use sp_executesql, could be something like this
EXECUTE sp_executesql
N'SELECT * FROM YouTable WHERE job_code = @job_code',
N'@job_code varchar(100)',
@job_code = @job_code;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
