Danny Stolle wrote:
Hi,
What you perhaps could use is the REGEXP usage in your where clause.
Try this:
update name set first_name=replace(first_name, '\\', '') where
first_name regexp '\\\\';
The fun thing is that when you put '\\' instead of the '\\\\' after the
regexp function it doesn't work. But this sure does.
Before you use the update, create a selection first with the regexp
function; if that works, your update will do fine.
Hope this little info helps you :-)
Danny
Jerry Swanson wrote:
I have 290 records in the database with backslashes. I want to remove the
backslashes. Why the query below doesn't remove backslashes?
update name set first_name = REPLACE(first_name,'\\\\','') where
first_name
like '%\\\\%';
You should be able to do this with either LIKE or REGEXP. The problem, I think,
was too many backslashes in the REPLACE part of the UPDATE. That is,
REPLACE(first_name,'\\\\','')
tells mysql to replace double backslashes (\\) with nothing, but won't have any
effect on single backslashes (\). I believe this is because the LIKE or REGEXP
pattern is parsed twice, while the REPLACE pattern is parsed once, as the
following example shows:
CREATE TABLE bst (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
txt CHAR(32));
INSERT INTO bst (txt)
VALUES ('Here\'s some text.'), ('Here is some more.'),
('This has a \\ in it.'), ('This one has 2 \\\\ in the middle.');
SELECT * FROM bst;
+----+----------------------------------+
| id | txt |
+----+----------------------------------+
| 1 | Here's some text. |
| 2 | Here is some more. |
| 3 | This has a \ in it. |
| 4 | This one has 2 \\ in the middle. |
+----+----------------------------------+
4 rows in set (0.00 sec)
SELECT * FROM bst WHERE txt LIKE '%\\\\%';
+----+----------------------------------+
| id | txt |
+----+----------------------------------+
| 3 | This has a \ in it. |
| 4 | This one has 2 \\ in the middle. |
+----+----------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM bst WHERE txt REGEXP '\\\\';
+----+----------------------------------+
| id | txt |
+----+----------------------------------+
| 3 | This has a \ in it. |
| 4 | This one has 2 \\ in the middle. |
+----+----------------------------------+
2 rows in set (0.01 sec)
UPDATE bst SET txt = REPLACE(txt, '\\\\', '');
Query OK, 1 row affected (0.00 sec)
Rows matched: 4 Changed: 1 Warnings: 0
SELECT * FROM bst;
+----+--------------------------------+
| id | txt |
+----+--------------------------------+
| 1 | Here's some text. |
| 2 | Here is some more. |
| 3 | This has a \ in it. |
| 4 | This one has 2 in the middle. |
+----+--------------------------------+
4 rows in set (0.00 sec)
UPDATE bst SET txt = REPLACE(txt, '\\', '');
Query OK, 1 row affected (0.00 sec)
Rows matched: 4 Changed: 1 Warnings: 0
SELECT * FROM bst;
+----+--------------------------------+
| id | txt |
+----+--------------------------------+
| 1 | Here's some text. |
| 2 | Here is some more. |
| 3 | This has a in it. |
| 4 | This one has 2 in the middle. |
+----+--------------------------------+
4 rows in set (0.00 sec)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
