删除MySQL中所有字段的前两个字符？
要删除所有字段的前两个字符，您需要使用SUBSTRING()MySQL中的function。语法如下-
UPDATE yourTableName SET yourColumnName=SUBSTRING(yourColumnName,3) WHERE yourCondition;
为了理解上述语法，让我们创建一个表。创建表的查询如下-
mysql> create table RemoveFirstTwoCharacterDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> StringValue varchar(30), -> PRIMARY KEY(Id) -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into RemoveFirstTwoCharacterDemo(StringValue) values('U:100');
mysql> insert into RemoveFirstTwoCharacterDemo(StringValue) values('S:20');
mysql> insert into RemoveFirstTwoCharacterDemo(StringValue) values('N/A');
mysql> insert into RemoveFirstTwoCharacterDemo(StringValue) values('T:8');
mysql> insert into RemoveFirstTwoCharacterDemo(StringValue) values('N/A');
mysql> insert into RemoveFirstTwoCharacterDemo(StringValue) values('W:99');使用select语句显示表中的所有记录。查询如下-
mysql> select *from RemoveFirstTwoCharacterDemo;
以下是输出-
+----+-------------+ | Id | StringValue | +----+-------------+ | 1 | U:100 | | 2 | S:20 | | 3 | N/A | | 4 | T:8 | | 5 | N/A | | 6 | W:99 | +----+-------------+ 6 rows in set (0.00 sec)
以下是删除前两个字符的查询-
mysql> update RemoveFirstTwoCharacterDemo set StringValue=SUBSTRING(StringValue,3) -> where StringValue <> 'N/A'; Rows matched: 4 Changed: 4 Warnings: 0
使用select语句再次检查表记录。查询如下-
mysql> select *from RemoveFirstTwoCharacterDemo;
以下是输出-
+----+-------------+ | Id | StringValue | +----+-------------+ | 1 | 100 | | 2 | 20 | | 3 | N/A | | 4 | 8 | | 5 | N/A | | 6 | 99 | +----+-------------+ 6 rows in set (0.00 sec)