severin wrote:
> Hi all,
>
> I try "update mybase set name = trim(name)"
>
> to skip spaces.
>
> But 'charriot return'
> (\13\10) are always here.
>
> How can i remove ( or select ) all invisible characteres??

The placement of the "whitespaces" are important when using trim(), it takes
only those that are in the beginning or/and end of the string.

so if you have a string like, "hello\n\rThis is my string", trim will give you
the string "hello\n\rThis is my string" as the charterers in question are in
the middle of the string.

http://www.php.net/manual/en/function.trim.php


If you want to trim characters in the middle of a string, you may want to use
something like eregi_replace().

$newstring=eregi_replace("\n\r",'',"hello\n\rThis is my string");
// $newstring="helloThis is my string"

http://www.php.net/manual/en/function.eregi-replace.php


--

//Aho

Thanks J.O. Aho for your answer,

My question is bad written.

Actually, i wonder about the difference between
"trim" : the php function
and "trim" the mysql function.

$mystr = trim( $mystr ); skip all white caracters (space, carriage
return,tab...)

but the sql request:
$sql = "update mybase set name = trim( name ) "
//(here trim is the mysql function)
mysql_query( $sql )

makes the field 'name' of 'mybase' whithout spaces but keeps carriage
return,tab... The mysql trim function only skips spaces.


to skip carriage in mysql i found:
$sql = "update mybase set name = replace( "\n","", name )";

but tab,line feed,NULL and other white caracters are still here...

and i have to do:
$sql = "update mybase set name = replace( "(tab?)","", name )";
$sql = "update mybase set name = replace( "(linefeed?)","", name )";
$sql = "update mybase set name = replace( " ","", name )";

to update my name filed whithout white caracteres.

I'm searching a mysql syntax to do all thouses updates in one only
query. Probably using regexp but i don't know how??

$sql = "update mybase set name = replace( "\n| |\r|\t","", name )";

J.O. Aho wrote:
> severin wrote:
>
>> Hi all,
>>
>> I try "update mybase set name = trim(name)"
>>
>> to skip spaces.
>>
>> But 'charriot return'
>> (\13\10) are always here.
>>
>> How can i remove ( or select ) all invisible characteres??
>
>
> The placement of the "whitespaces" are important when using trim(), it
> takes only those that are in the beginning or/and end of the string.
>
> so if you have a string like, "hello\n\rThis is my string", trim will
> give you the string "hello\n\rThis is my string" as the charterers in
> question are in the middle of the string.
>
> http://www.php.net/manual/en/function.trim.php
>
>
> If you want to trim characters in the middle of a string, you may want
> to use something like eregi_replace().
>
> $newstring=eregi_replace("\n\r",'',"hello\n\rThis is my string");
> // $newstring="helloThis is my string"
>
> http://www.php.net/manual/en/function.eregi-replace.php
>
>

severin wrote:
> Thanks J.O. Aho for your answer,
>
> My question is bad written.
>
> Actually, i wonder about the difference between
> "trim" : the php function
> and "trim" the mysql function.

OK, then the answer should be different.

> but the sql request:
> $sql = "update mybase set name = trim( name ) "
> //(here trim is the mysql function)
> mysql_query( $sql )
>
> makes the field 'name' of 'mybase' whithout spaces but keeps carriage
> return,tab... The mysql trim function only skips spaces.

The MySQL documentation is few wording about trim
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

I guess that it don't regard \n and \r as whitespaces


--

//Aho

In article <45acc85d$0$297$426a74cc [at] news.free.fr>, severin
<severin.richard [at] free.fr> wrote:

> I'm searching a mysql syntax to do all thouses updates in one only
> query. Probably using regexp but i don't know how??


As far as I am aware, the regexp function in MySQL is only for string
comparison and does not work for substitutions. You will have to manage
this via PHP or string together a query with a ton of replace() to
handle all your replacements.

On the other hand, this is an example of how you can do it in PHP:

$str = "\t\r\ntest\ning\r123\r\n";
echo preg_replace( '/[\r\n \t]+/', "", $str );

--
Koncept <<
"The snake that cannot shed its skin perishes. So do the spirits who are
prevented from changing their opinions; they cease to be a spirit." -Nietzsche

Thanks J.O. Aho for your answer,

My question is bad written.

Actually, i wonder about the difference between
"trim" : the php function
and "trim" the mysql function.

$mystr = trim( $mystr ); skip all white caracters (space, carriage
return,tab...)

but the sql request:
$sql = "update mybase set name = trim( name ) "
//(here trim is the mysql function)
mysql_query( $sql )

makes the field 'name' of 'mybase' whithout spaces but keeps carriage
return,tab... The mysql trim function only skips spaces.


to skip carriage in mysql i found:
$sql = "update mybase set name = replace( "\n","", name )";

but tab,line feed,NULL and other white caracters are still here...

and i have to do:
$sql = "update mybase set name = replace( "(tab?)","", name )";
$sql = "update mybase set name = replace( "(linefeed?)","", name )";
$sql = "update mybase set name = replace( " ","", name )";

to update my name filed whithout white caracteres.

I'm searching a mysql syntax to do all thouses updates in one only
query. Probably using regexp but i don't know how??

$sql = "update mybase set name = replace( "\n| |\r|\t","", name )";

J.O. Aho wrote:
> severin wrote:
>
>> Hi all,
>>
>> I try "update mybase set name = trim(name)"
>>
>> to skip spaces.
>>
>> But 'charriot return'
>> (\13\10) are always here.
>>
>> How can i remove ( or select ) all invisible characteres??
>
>
> The placement of the "whitespaces" are important when using trim(), it
> takes only those that are in the beginning or/and end of the string.
>
> so if you have a string like, "hello\n\rThis is my string", trim will
> give you the string "hello\n\rThis is my string" as the charterers in
> question are in the middle of the string.
>
> http://www.php.net/manual/en/function.trim.php
>
>
> If you want to trim characters in the middle of a string, you may want
> to use something like eregi_replace().
>
> $newstring=eregi_replace("\n\r",'',"hello\n\rThis is my string");
> // $newstring="helloThis is my string"
>
> http://www.php.net/manual/en/function.eregi-replace.php
>
>

severin wrote:
> Thanks J.O. Aho for your answer,
>
> My question is bad written.
>
> Actually, i wonder about the difference between
> "trim" : the php function
> and "trim" the mysql function.

OK, then the answer should be different.

> but the sql request:
> $sql = "update mybase set name = trim( name ) "
> //(here trim is the mysql function)
> mysql_query( $sql )
>
> makes the field 'name' of 'mybase' whithout spaces but keeps carriage
> return,tab... The mysql trim function only skips spaces.

The MySQL documentation is few wording about trim
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

I guess that it don't regard \n and \r as whitespaces


--

//Aho

In article <45acc85d$0$297$426a74cc [at] news.free.fr>, severin
<severin.richard [at] free.fr> wrote:

> I'm searching a mysql syntax to do all thouses updates in one only
> query. Probably using regexp but i don't know how??


As far as I am aware, the regexp function in MySQL is only for string
comparison and does not work for substitutions. You will have to manage
this via PHP or string together a query with a ton of replace() to
handle all your replacements.

On the other hand, this is an example of how you can do it in PHP:

$str = "\t\r\ntest\ning\r123\r\n";
echo preg_replace( '/[\r\n \t]+/', "", $str );

--
Koncept <<
"The snake that cannot shed its skin perishes. So do the spirits who are
prevented from changing their opinions; they cease to be a spirit." -Nietzsche