Removing space in varchar

Hi,

i have a problem to make a transtype

from field XX varchar(20), example :
19 200 340,56

to field YY integer, required :
19200340

I make two replace
- first for replacing the , by a .
- second for replacing the space by '' (nothing)

and a cast :

insert into table2
select cast(replace(replace(XX,',','.'),' ','') as unsigned) from table
1 ;

The problem is the first replace. The space is not recognized. Even for
example with others commands related to string. If I put any other
caracter, it works.

Does anyone have any solution ?
Thanks a lot.

Re: Removing space in varchar

tcfin wrote:

> Hi,
>
> i have a problem to make a transtype
>
> from field XX varchar(20), example :
> 19 200 340,56
>
> to field YY integer, required :
> 19200340
>
> I make two replace
> - first for replacing the , by a .
> - second for replacing the space by '' (nothing)
>
> and a cast :
>
> insert into table2
> select cast(replace(replace(XX,',','.'),' ','') as unsigned) from table
> 1 ;
>
> The problem is the first replace. The space is not recognized. Even for
> example with others commands related to string. If I put any other
> caracter, it works.
>
> Does anyone have any solution ?
> Thanks a lot.
>
mysql> insert into c values ('1 2 3 4,5'),('1 2 3 5,5'),('1 2 3 6,5');
Query OK, 3 rows affected (0.20 sec)

mysql> select * from c;
+-----------+
| a |
+-----------+
| 1 2 3 4,5 |
| 1 2 3 5,5 |
| 1 2 3 6,5 |
+-----------+
3 rows in set (0.01 sec)

YOUR QUERY:
mysql> select cast(replace(replace(a, ',' , '.'),' ','') as unsigned) as X from c;
+------+
| X |
+------+
| 1234 |
| 1235 |
| 1236 |
+------+
3 rows in set, 3 warnings (0.01 sec)
============================================================ ======

You need to use the proper data-type if you want to preserve the decimal place(s).

mysql> select cast(replace(replace(a, ',' , '.'),' ','') as decimal(7,1)) as X
from c;
+--------+
| X |
+--------+
| 1234.5 |
| 1235.5 |
| 1236.5 |
+--------+
3 rows in set (0.01 sec)



--
Michael Austin.
Database Consultant

Re: Removing space in varchar

Michael Austin wrote:
> tcfin wrote:
>
> > Hi,
> >
> > i have a problem to make a transtype
> >
> > from field XX varchar(20), example :
> > 19 200 340,56
> >
> > to field YY integer, required :
> > 19200340
> >
> > I make two replace
> > - first for replacing the , by a .
> > - second for replacing the space by '' (nothing)
> >
> > and a cast :
> >
> > insert into table2
> > select cast(replace(replace(XX,',','.'),' ','') as unsigned) from table
> > 1 ;
> >
> > The problem is the first replace. The space is not recognized. Even for
> > example with others commands related to string. If I put any other
> > caracter, it works.
> >
> > Does anyone have any solution ?
> > Thanks a lot.
> >

You may have a problem with the version you are using. I am running
server version: 5.1.11-beta-log. If you get different results, then
you file a bug report - for your specific platform and version.
Upgrade to the latest, stable version

>when I make first a simple query on the field a (varchar(20))
>
>select replace(a,' ','') as X from table
>
>the result is unchanged, the spaces are not removed...


Does this work?

select cast(replace(replace('193 456,78',',' , '.'),' ','')
as decimal(7,2)) as X;

+-----------+
| X |
+-----------+
| 193456.78 |
+-----------+


Here is my "test" case.

mysql> create table a (a varchar(20));
Query OK, 0 rows affected (1.01 sec)

mysql> insert into a values ('193 456,78'),('1 193 456,87');
Query OK, 2 rows affected (0.29 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from a;
+--------------+
| a |
+--------------+
| 193 456,78 |
| 1 193 456,87 |
+--------------+
2 rows in set (0.03 sec)

mysql> select replace(a,' ','') as X from a;
+------------+
| X |
+------------+
| 193456,78 |
| 1193456,87 |
+------------+
2 rows in set (0.01 sec)

mysql> select cast(replace(replace(a,',' , '.'),' ','')
-> as decimal(7,2)) as X from a;
+------------+
| X |
+------------+
| 193456.78 |
| 1193456.87 |
+------------+
2 rows in set (0.02 sec)


mysql> select cast(replace(replace(a,',' , '.'),' ','')
-> as decimal(7,1)) as X from a;
+-----------+
| X |
+-----------+
| 193456.8 |
| 1193456.9 |
+-----------+

Re: Removing space in varchar

Thank you for your help.
The test is OK. SO it is not a problem of MySql version.
But my problem still exists.
I think the problem is my data are imported from an excel file.
Is it possible that the space character I see is not a space but
another character ? This would explain that MySql does not recognize
the space within the string.
How can I check this ?

Thanks again.

onedbguru a =E9crit :

> Michael Austin wrote:
> > tcfin wrote:
> >
> > > Hi,
> > >
> > > i have a problem to make a transtype
> > >
> > > from field XX varchar(20), example :
> > > 19 200 340,56
> > >
> > > to field YY integer, required :
> > > 19200340
> > >
> > > I make two replace
> > > - first for replacing the , by a .
> > > - second for replacing the space by '' (nothing)
> > >
> > > and a cast :
> > >
> > > insert into table2
> > > select cast(replace(replace(XX,',','.'),' ','') as unsigned) from tab=
le
> > > 1 ;
> > >
> > > The problem is the first replace. The space is not recognized. Even f=
or
> > > example with others commands related to string. If I put any other
> > > caracter, it works.
> > >
> > > Does anyone have any solution ?
> > > Thanks a lot.
> > >
>
> You may have a problem with the version you are using. I am running
> server version: 5.1.11-beta-log. If you get different results, then
> you file a bug report - for your specific platform and version.
> Upgrade to the latest, stable version
>
> >when I make first a simple query on the field a (varchar(20))
> >
> >select replace(a,' ','') as X from table
> >
> >the result is unchanged, the spaces are not removed...
>
>
> Does this work?
>
> select cast(replace(replace('193 456,78',',' , '.'),' ','')
> as decimal(7,2)) as X;
>
> +-----------+
> | X |
> +-----------+
> | 193456.78 |
> +-----------+
>
>
> Here is my "test" case.
>
> mysql> create table a (a varchar(20));
> Query OK, 0 rows affected (1.01 sec)
>
> mysql> insert into a values ('193 456,78'),('1 193 456,87');
> Query OK, 2 rows affected (0.29 sec)
> Records: 2 Duplicates: 0 Warnings: 0
>
> mysql> select * from a;
> +--------------+
> | a |
> +--------------+
> | 193 456,78 |
> | 1 193 456,87 |
> +--------------+
> 2 rows in set (0.03 sec)
>
> mysql> select replace(a,' ','') as X from a;
> +------------+
> | X |
> +------------+
> | 193456,78 |
> | 1193456,87 |
> +------------+
> 2 rows in set (0.01 sec)
>
> mysql> select cast(replace(replace(a,',' , '.'),' ','')
> -> as decimal(7,2)) as X from a;
> +------------+
> | X |
> +------------+
> | 193456.78 |
> | 1193456.87 |
> +------------+
> 2 rows in set (0.02 sec)
>
>
> mysql> select cast(replace(replace(a,',' , '.'),' ','')
> -> as decimal(7,1)) as X from a;
> +-----------+
> | X |
> +-----------+
> | 193456.8 |
> | 1193456.9 |
> +-----------+

Re: Removing space in varchar

tcfin wrote:

> Thank you for your help.
> The test is OK. SO it is not a problem of MySql version.
> But my problem still exists.
> I think the problem is my data are imported from an excel file.
> Is it possible that the space character I see is not a space but
> another character ? This would explain that MySql does not recognize
> the space within the string.
> How can I check this ?
>
> Thanks again.
>
> onedbguru a écrit :
>
>
>>Michael Austin wrote:
>>
>>>tcfin wrote:
>>>
>>>
>>>>Hi,
>>>>
>>>>i have a problem to make a transtype
>>>>
>>>>from field XX varchar(20), example :
>>>>19 200 340,56
>>>>
>>>>to field YY integer, required :
>>>>19200340
>>>>
>>>>I make two replace
>>>> - first for replacing the , by a .
>>>>- second for replacing the space by '' (nothing)
>>>>
>>>>and a cast :
>>>>
>>>>insert into table2
>>>>select cast(replace(replace(XX,',','.'),' ','') as unsigned) from table
>>>>1 ;
>>>>
>>>>The problem is the first replace. The space is not recognized. Even for
>>>>example with others commands related to string. If I put any other
>>>>caracter, it works.
>>>>
>>>>Does anyone have any solution ?
>>>>Thanks a lot.
>>>>
>>
>>You may have a problem with the version you are using. I am running
>>server version: 5.1.11-beta-log. If you get different results, then
>>you file a bug report - for your specific platform and version.
>>Upgrade to the latest, stable version
>>
>>
>>>when I make first a simple query on the field a (varchar(20))
>>>
>>>select replace(a,' ','') as X from table
>>>
>>>the result is unchanged, the spaces are not removed...
>>
>>
>>Does this work?
>>
>>select cast(replace(replace('193 456,78',',' , '.'),' ','')
>>as decimal(7,2)) as X;
>>
>>+-----------+
>>| X |
>>+-----------+
>>| 193456.78 |
>>+-----------+
>>
>>
>>Here is my "test" case.
>>
>>mysql> create table a (a varchar(20));
>>Query OK, 0 rows affected (1.01 sec)
>>
>>mysql> insert into a values ('193 456,78'),('1 193 456,87');
>>Query OK, 2 rows affected (0.29 sec)
>>Records: 2 Duplicates: 0 Warnings: 0
>>
>>mysql> select * from a;
>>+--------------+
>>| a |
>>+--------------+
>>| 193 456,78 |
>>| 1 193 456,87 |
>>+--------------+
>>2 rows in set (0.03 sec)
>>
>>mysql> select replace(a,' ','') as X from a;
>>+------------+
>>| X |
>>+------------+
>>| 193456,78 |
>>| 1193456,87 |
>>+------------+
>>2 rows in set (0.01 sec)
>>
>>mysql> select cast(replace(replace(a,',' , '.'),' ','')
>> -> as decimal(7,2)) as X from a;
>>+------------+
>>| X |
>>+------------+
>>| 193456.78 |
>>| 1193456.87 |
>>+------------+
>>2 rows in set (0.02 sec)
>>
>>
>>mysql> select cast(replace(replace(a,',' , '.'),' ','')
>> -> as decimal(7,1)) as X from a;
>>+-----------+
>>| X |
>>+-----------+
>>| 193456.8 |
>>| 1193456.9 |
>>+-----------+
>
>

it could be a tab? (hex "33 09" (


mysql> select * from a
-> ;
+--------------+
| a |
+--------------+
| 193 456,78 |
+--------------+
2 rows in set (0.15 sec)

mysql> select hex(a) from a;
+--------------------------+
| hex(a) |
+--------------------------+
| 313933203435362C3738 |
+--------------------------+

Let's take the value and break it up...

31 39 33 20 34 35 36 2C 37 38
1 9 3 sp 4 5 6 , 7 8

mysql> insert into a values ( concat('193','\t','456,78'));
Query OK, 1 row affected (0.11 sec)

mysql> select hex(a) from a;
+----------------------+
| hex(a) |
+----------------------+
| 313933093435362C3738 |
+----------------------+
1 row in set (0.01 sec)

31 39 33 09 34 35 36 2C 37 38
1 9 3 tab 4 5 6 , 7 8


--
Michael Austin.
Database Consultant