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(re place(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]

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(re place(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(re place(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(re place(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]

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(re place(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(re place('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(re place(a,',' , '.'),' ','')
-as decimal(7,2)) as X from a;
+------------+
| X |
+------------+
| 193456.78 |
| 1193456.87 |
+------------+
2 rows in set (0.02 sec)


mysqlselect cast(replace(re place(a,',' , '.'),' ','')
-as decimal(7,1)) as X from a;
+-----------+
| X |
+-----------+
| 193456.8 |
| 1193456.9 |
+-----------+

[tcfin]

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 é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(re place(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(re place('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(re place(a,',' , '.'),' ','')
-as decimal(7,2)) as X from a;
+------------+
| X |
+------------+
| 193456.78 |
| 1193456.87 |
+------------+
2 rows in set (0.02 sec)
>
>
mysqlselect cast(replace(re place(a,',' , '.'),' ','')
-as decimal(7,1)) as X from a;
+-----------+
| X |
+-----------+
| 193456.8 |
| 1193456.9 |
+-----------+

[Michael Austin]

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(re place(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(re place('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(re place(a,',' , '.'),' ','')
> -as decimal(7,2)) as X from a;
>>+------------+
>>| X |
>>+------------+
>>| 193456.78 |
>>| 1193456.87 |
>>+------------+
>>2 rows in set (0.02 sec)
>>
>>
>>mysqlselect cast(replace(re place(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) |
+--------------------------+
| 313933203435362 C3738 |
+--------------------------+

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) |
+----------------------+
| 313933093435362 C3738 |
+----------------------+
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