how to get rid of '\' from the database?

User, date Message
Written by gilgal
5 years ago
Category: General
32 posts since Wed, 12 Dec 07 		I have inserted many words which have an apostrophe such as John's, Peter's...by putting \ with it. How do I get rid of that?
Written by ansgar
5 years ago
3966 posts since Fri, 07 Apr 06

UPDATE mytable SET mycolumn = REPLACE(mycolumn, '\\
'', '\'')



Just notice you have to escape a single quote and a backslash using a backslash!
Written by gilgal
5 years ago
32 posts since Wed, 12 Dec 07 		any double quote needed?

UPDATE mytable SET mycolumn = REPLACE(mycolumn, '"\\'"', '"\'"')
Written by gilgal
5 years ago
32 posts since Wed, 12 Dec 07 		oh no I don't think so.
Written by gilgal
5 years ago
32 posts since Wed, 12 Dec 07 		Is that all that's needed or do I have to put WHERE and make loops?
Written by gilgal
5 years ago
32 posts since Wed, 12 Dec 07 		It doesn't seem to work.

$sql = "UPDATE kjv SET text_data = REPLACE(text_data, '\', '')";
Written by gilgal
5 years ago
32 posts since Wed, 12 Dec 07 		I have:

<?php

require_once('cat/mysql.php');
$sql = "UPDATE bible SET text_data = REPLACE(text_data, '\', '')";
//$sql = "UPDATE bible SET text_data = '36' WHERE FirstName = 'Peter' AND LastName = 'Griffin'";

mysql_query($sql);

//mysql_close($con);

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<?php

$sql2 = "SELECT text_data FROM bible WHERE text_data LIKE '\'";

$result = mysql_query($sql2);

while($row = $result)
{
echo $row['text_data'];
echo "<br />";
}

mysql_close($con);

?>
</body>
</html>
Written by ansgar
5 years ago
3966 posts since Fri, 07 Apr 06 		Find the difference:

Your code:

UPDATE kjv SET text_data = REPLACE(text_data, '\', '')



My proposal:

UPDATE kjv SET text_data = REPLACE(text_data, '\\'', '\'')
Written by gilgal
5 years ago
32 posts since Wed, 12 Dec 07 		It's not showing any errors but when I check it hasn't corrected.
Written by ansgar
5 years ago
3966 posts since Fri, 07 Apr 06 		The fact that it doesn't give you any error does not mean it works like you expect it to do. This code works for me in HeidiSQL:

select REPLACE('\\\'hi\\\'', '\\\'', '\'')
Written by kalvaro
5 years ago
442 posts since Thu, 29 Nov 07

gilgal wrote: It's not showing any errors



It won't unless you instruct PHP to display them:

ini_set('display_errors', 1);
error_reporting(E_ALL | E_STRICT);

//...

mysql_query($sql) or die(mysql_error());
 

Please login to leave a reply, or register at first.