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: Quote:
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 | | | | re: Removing space in varchar
Michael Austin wrote: Quote:
tcfin wrote:
> Quote:
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 Quote:
>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 |
+-----------+ | | | | 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 : Quote:
Michael Austin wrote: Quote:
tcfin wrote: Quote:
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
> Quote:
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 |
+-----------+
| | | | re: Removing space in varchar
tcfin wrote: Quote:
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 :
>
> Quote:
>>Michael Austin wrote:
>> Quote:
>>>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
>>
>> Quote:
>>>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 |  |
Similar MySQL Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,223 network members.
|
