Removing space in varchar

[tcfin]

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.

[Michael Austin]

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.

mysqlinsert 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)

mysqlselect * 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:
mysqlselect 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).

mysqlselect 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

[onedbguru]

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.

mysqlcreate table a (a varchar(20));
Query OK, 0 rows affected (1.01 sec)

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

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

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

mysqlselect cast(replace(replace(a,',' , '.'),' ','')
-as decimal(7,2)) as X from a;
+------------+
| X |
+------------+
| 193456.78 |
| 1193456.87 |
+------------+
2 rows in set (0.02 sec)
mysqlselect cast(replace(replace(a,',' , '.'),' ','')
-as decimal(7,1)) as X from a;
+-----------+
| X |
+-----------+
| 193456.8 |
| 1193456.9 |
+-----------+

[tcfin]

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.

mysqlcreate table a (a varchar(20));
Query OK, 0 rows affected (1.01 sec)

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

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

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

mysqlselect cast(replace(replace(a,',' , '.'),' ','')
-as decimal(7,2)) as X from a;
+------------+
| X |
+------------+
| 193456.78 |
| 1193456.87 |
+------------+
2 rows in set (0.02 sec)
mysqlselect cast(replace(replace(a,',' , '.'),' ','')
-as decimal(7,1)) as X from a;
+-----------+
| X |
+-----------+
| 193456.8 |
| 1193456.9 |
+-----------+

[Michael Austin]

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.

mysqlcreate table a (a varchar(20));
Query OK, 0 rows affected (1.01 sec)

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

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

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

mysqlselect cast(replace(replace(a,',' , '.'),' ','')
-as decimal(7,2)) as X from a;
+------------+
| X |
+------------+
| 193456.78 |
| 1193456.87 |
+------------+
2 rows in set (0.02 sec)
mysqlselect 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" (
mysqlselect * from a
-;
+--------------+
| a |
+--------------+
| 193 456,78 |
+--------------+
2 rows in set (0.15 sec)

mysqlselect 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

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

mysqlselect 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