distal-attribute
distal-attribute
distal-attribute
distal-attribute

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

gilgal posted 7 years ago in General
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?
ansgar posted 7 years ago

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



Just notice you have to escape a single quote and a backslash using a backslash!
gilgal posted 7 years ago
any double quote needed?

UPDATE mytable SET mycolumn = REPLACE(mycolumn, '"\\'"', '"\'"')
gilgal posted 7 years ago
oh no I don't think so.
gilgal posted 7 years ago
Is that all that's needed or do I have to put WHERE and make loops?
gilgal posted 7 years ago
It doesn't seem to work.

$sql = "UPDATE kjv SET text_data = REPLACE(text_data, '\', '')";

gilgal posted 7 years ago
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>

ansgar posted 7 years ago
Find the difference:

Your code:

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



My proposal:

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

gilgal posted 7 years ago
It's not showing any errors but when I check it hasn't corrected.
ansgar posted 7 years ago
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\\\'', '\\\'', '\'')

kalvaro posted 7 years ago

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.