Re: Filtering non-ascii characters from mysql data, null, tab etc

[Michael Stassen]

zzapper wrote:
On Fri, 11 Feb 2005 12:46:29 +0100 (CET),  wrote:
Tom adapting your script,
create table test (txt varchar(255)) Type=MyISAM;
insert into test values('Some Text\nand some more');
update test set txt = replace(txt,'\n','');
BTW 
\n = null  

\0 seems to be something else
Turns out my rotten character (they all seem to display as a hollow box) was a 
\r
thanx
zzapper (vim, cygwin, wiki & zsh)
--
No.  \n is a newline, \r is a return, and \0 is the null character C uses to 
terminate strings.  Continuing your example:

mysql> CREATE TABLE test (id INT, txt VARCHAR(255));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test VALUES (1, 'Some Text\0 and some more'),
    ->                         (2, 'Some Text\nand some more');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM test;
+------+--------------------------+
| id   | txt                      |
+------+--------------------------+
|    1 | Some Text                |
|    2 | Some Text
and some more  |
+------+--------------------------+
2 rows in set (0.00 sec)
mysql> UPDATE test SET txt = REPLACE(txt, '\0', '');
Query OK, 1 row affected (0.13 sec)
Rows matched: 2  Changed: 1  Warnings: 0
mysql> UPDATE test SET txt = REPLACE(txt, '\n', ' ');
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0
mysql> SELECT * FROM test;
+------+-------------------------+
| id   | txt                     |
+------+-------------------------+
|    1 | Some Text and some more |
|    2 | Some Text and some more |
+------+-------------------------+
2 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]