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

32 posts

[expired user #3277] posted 16 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?

10152 posts

ansgar posted 16 years ago

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

Just notice you have to escape a single quote and a backslash using a backslash!

32 posts

[expired user #3277] posted 16 years ago

any double quote needed?

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

32 posts

[expired user #3277] posted 16 years ago

oh no I don't think so.

32 posts

[expired user #3277] posted 16 years ago

Is that all that's needed or do I have to put WHERE and make loops?

32 posts

[expired user #3277] posted 16 years ago

It doesn't seem to work.

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

32 posts

[expired user #3277] posted 16 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>

10152 posts

ansgar posted 16 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, '\\'', '\'')

32 posts

[expired user #3277] posted 16 years ago

It's not showing any errors but when I check it hasn't corrected.

10152 posts

ansgar posted 16 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\\\'', '\\\'', '\'')

959 posts

kalvaro posted 16 years ago

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());