CODEWITHSUNDEEP
mysqlstored-procedures
user2137982
user2137982

Reputation: 33

Difficulty of removing the apostrophe in MySQL stored procedure

I am trying to remove apostrophes, below is the whole procedure.

BEGIN
SET _myparam = replace(_myparam, "'", '');
UPDATE `Table` SET NAME=_myparam WHERE UID=_someotherparam;
END

I also tried:

SET _myparam = replace(_myparam, '\'', '');
SET _myparam = replace(_myparam, '''', ''); 
SET _myparam = replace(_myparam, CHAR(39), '');
SET _myparam = replace(_myparam, '%\'%', '');

This seems to work with any other character but not the apostrophe. Nothing here seems to remove the apostrophe character. Any help would be appreciated.

I Googled everything I could think of, and have been pulling out hair for hours. Thanks!

Upvotes: 3

Views: 1643

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562310

You can also use two single-quotes which means one literal single-quote in standard SQL.

REPLACE("I wan't to be alone", '''', '')

FYI, in standard SQL, double-quotes are for delimiting identifiers like table names and column names. Single quotes are for delimiting strings and dates. It's a non-standard MySQLism to treat double-quotes as the same as single-quotes. But I use it in the example above, so I don't have to escape the apostrophe.

I also tested:

USE test;
DROP TABLE IF EXISTS `Table`;
CREATE TABLE `Table` (
 UID INT PRIMARY KEY,
 NAME TEXT
);
INSERT INTO `Table` VALUES (1, 'foo');

DROP PROCEDURE IF EXISTS q;

DELIMITER !!
CREATE PROCEDURE q(_myparam TEXT, _someotherparam INT)
BEGIN
 SET _myparam = REPLACE(_myparam, '''', '');
 UPDATE `Table` SET NAME=_myparam WHERE UID=_someotherparam;
END!!
DELIMITER ;

CALL q("I wan't to be alone", 1);

SELECT * FROM `Table`;

+-----+--------------------+
| UID | NAME               |
+-----+--------------------+
|   1 | I want to be alone |
+-----+--------------------+

It seems to work just fine. I also tested using VARCHAR(20) for _myparam and that works too.

Upvotes: 1

wired00
wired00

Reputation: 14438

I just tested this standard select out and it works. have you just tried

'\'' ?

ie, this works in standard select:

SELECT REPLACE("hi th'ere", '\'', '');

this does not:

SELECT REPLACE("hi th'ere",'%\'%', '');

of course this also works:

SELECT REPLACE("hi th'ere","'", '');

Edit: Here is the solution as MySQL Stored Procedure.

DELIMITER // 
CREATE PROCEDURE TestStoredProc(IN _myvariable VARCHAR(25)) 
BEGIN
SET _myvariable = REPLACE(_myvariable, '\'', '');
SELECT _myvariable;
END // 
DELIMITER ;

and

CALL TestStoredProc("hi th'ere");

Upvotes: 1

Related Questions