有数据表A
------------------------------------
字段 a b
数据 2w342(dad) 3434
三大殿(4rf) 898
大哥的(大点) 34242
dada 78787
dahfi dhj
da33(dada) fhsfk
...... ......
...... ......
-------------------------------------求SQL语句,将a字段的数据的括号及括号里的东西删除掉。及变成:----------------------------------
字段 a b
数据 2w342 3434
三大殿 898
大哥的 34242
dada 78787
dahfi dhj
da33 fhsfk
...... ......
...... ......
----------------------------------
------------------------------------
字段 a b
数据 2w342(dad) 3434
三大殿(4rf) 898
大哥的(大点) 34242
dada 78787
dahfi dhj
da33(dada) fhsfk
...... ......
...... ......
-------------------------------------求SQL语句,将a字段的数据的括号及括号里的东西删除掉。及变成:----------------------------------
字段 a b
数据 2w342 3434
三大殿 898
大哥的 34242
dada 78787
dahfi dhj
da33 fhsfk
...... ......
...... ......
----------------------------------
-> replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(a,'(',''),')',''),'(',''),')',''),'0',
''),'9',''),'8',''),'7',''),'6',''),'5',''),'4',''),'3',''),'2',''),'1','') as k -> from t_drupalcn;
+----------------+-------+------------+
| a | b | k |
+----------------+-------+------------+
| 2w342(dad) | 3434 | wdad |
| 三大殿(4rf) | 898 | 三大殿rf |
| 大哥的(大点) | 34242 | 大哥的大点 |
| dada | 78787 | dada |
| dahfi | dhj | dahfi |
| da33(dada) | fhsfk | dadada |
+----------------+-------+------------+
6 rows in set (0.01 sec)mysql>
------------------------------------
字段 a b
数据 2w342(dad) 3434
三大殿(4rf) 898
大哥的(大点) 34242
dada 78787
dahfi dhj
da33(dada) fhsfk
...... ......
...... ......
-------------------------------------操作后变成:----------------------------------
字段 a b
数据 2w342 3434
三大殿 898
大哥的 34242
dada 78787
dahfi dhj
da33 fhsfk
...... ......
...... ......
----------------------------------
-> SUBSTRING_INDEX(replace(a,'(','('),'(',1) as a1 , b
-> from t_drupalcn;
+--------+-------+
| a1 | b |
+--------+-------+
| 2w342 | 3434 |
| 三大殿 | 898 |
| 大哥的 | 34242 |
| dada | 78787 |
| dahfi | dhj |
| da33 | fhsfk |
+--------+-------+
6 rows in set (0.00 sec)mysql>
;
另外这个mysql> select
-> SUBSTRING_INDEX(replace(a,'(','('),'(',1) as a1 , b
-> from t_drupalcn;
只是select 出来了,但是好像数据表中的值仍然没更新啊
更新的话要用update t_drupalcn
set a= SUBSTRING_INDEX(replace(a,'(','('),'(',1) ;
这是第三遍问你相同的问题了。还需要再问几次?
show create table 报错表例如:
update t.c set aa = SUBSTRING_INDEX(aa,'(',1) ;
现在的问题就是,如果那个括号是中文括号,就会出错,英文就不会出错
+--------------------------+-----------------------------
| Variable_name | Value
+--------------------------+-----------------------------
| character_set_client | latin1
| character_set_connection | latin1
| character_set_database | latin1
| character_set_filesystem | binary
| character_set_results | latin1
| character_set_server | latin1
| character_set_system | utf8
| character_sets_dir | C:\Program Files\MySQL\MySQL
+--------------------------+-----------------------------
8 rows in set (0.00 sec)mysql> show create table t_drupalcn;
+------------+-------------------------------------------
| Table | Create Table
+------------+-------------------------------------------
| t_drupalcn | CREATE TABLE `t_drupalcn` (
`a` varchar(20) DEFAULT NULL,
`b` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+-------------------------------------------
1 row in set (0.00 sec)mysql>
set names 'utf8';
update ....不行的话,再试
set names 'gbk';
update ...再不行再换
set names 'latin1';
update ...
当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html