Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to pass any character (escape characters) to a Stored Procedure

Posted on 2014-07-14
1
Medium Priority
?
1,256 Views
Last Modified: 2014-07-14
Hi.
I have the following how can I pass any character to the following - a double quote breaks it.
ths

CREATE  PROCEDURE `sproc_Search_All`(IN inQ varchar(100))
 BEGIN

 DECLARE qs VARCHAR(5000);

 set inQ=replace(inQ,'''','''''');


 set @qs = "SELECT *  FROM CUSTOMERS "

 set @qs = concat(@qs," AND ( ", "NAME  like '%", inQ, "%' ");

 set @qs = concat(@qs,") ORDER BY NAME ");


 PREPARE preparedstatement FROM @qs ;

 EXECUTE preparedstatement;

 DEALLOCATE PREPARE preparedstatement;
0
Comment
Question by:JElster
1 Comment
 
LVL 12

Accepted Solution

by:
Nathan Riley earned 1960 total points
ID: 40194881
Escape the quotes with \

MySQL recognizes the following escape sequences.
\0  An ASCII NUL (0x00) character.
\'  A single quote (“'”) character.
\"  A double quote (“"”) character.
\b  A backspace character.
\n  A newline (linefeed) character.
\r  A carriage return character.
\t  A tab character.
\Z  ASCII 26 (Control-Z). See note following the table.
\\  A backslash (“\”) character.
\%  A “%” character. See note following the table.
\_  A “_” character. See note following the table.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month20 days, 23 hours left to enroll

810 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