将表中某字段的所有非空值的最后一位截取掉

update 表名 set 字段=substr(字段,1,length(字段)-1) where 字段 IS NOT NULL AND 字段!=''

 

实例：

# 直接替换为空
UPDATE tracking SET tracking_info = REPLACE( tracking_info,  ';UPS:',  '' ) WHERE tracking_info LIKE  '%;UPS:%'

# ;UPS: 长度是 5
UPDATE tracking SET tracking_info = CONCAT( LEFT( tracking_info, LENGTH( tracking_info ) -5 ) ,  '' ) WHERE  `tracking_info` LIKE  '%;UPS:' 
# ;UPS: 长度是 5;  替换成 ups
UPDATE tracking SET tracking_info = CONCAT( LEFT( tracking_info, LENGTH( tracking_info ) -5 ) ,  'ups' ) WHERE  `tracking_info` LIKE  '%;UPS:' 

# ;USPS: 长度是 6
UPDATE tracking SET tracking_info = CONCAT( LEFT( tracking_info, LENGTH( tracking_info ) -6 ) ,  '' ) WHERE  `tracking_info` LIKE  '%;USPS:' 

# ;Amazon: 长度是 8
UPDATE tracking SET tracking_info = CONCAT( LEFT( tracking_info, LENGTH( tracking_info ) -8 ) ,  '' ) WHERE  `tracking_info` LIKE  '%;Amazon:'

 

 

MySQL 正则表达式

在前面的章节我们已经了解到MySQL可以通过 LIKE …% 来进行模糊匹配。

MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

如果您了解PHP或Perl，那么操作起来就非常简单，因为MySQL的正则表达式匹配与这些脚本的类似。

下表中的正则模式可应用于 REGEXP 操作符中。

 

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
.	匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用象 ‘[.\n]’ 的模式。
[…]	字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…]	负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1|p2|p3	匹配 p1 或 p2 或 p3。例如，’z|food’ 能匹配 “z” 或 “food”。'(z|f)ood’ 则匹配 “zood” 或 “food”。
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，’zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，’o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

 

基本字符匹配

检索column1_name包含文本1000的所有行

SELECT column1_name,column2_name FROM table WHERE column1_name REGEXP '1000'

返回所有包含x000的行

SELECT column1_name,column2_name FROM table WHERE column1_name REGEXP '.000'

.是正则表达式中的一个特殊字符，它表示匹配任意字符

REGEXP和LIKE非常相似，但是他们也有区别：
LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会匹配它(除非使用通配符)
REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行会被返回(使用^和$定位符也可以使REGEXP匹配整个列值，起到和LIKE一样的作用。用法：REGEXP ‘^1000$’)

MYSQL中的正则表达式默认不区分大小写，要想区分大小写，则使用 BINARY 关键字，例如：WHERE column_name REGEXP BINARY ‘asDF’;

 

进行OR匹配

为搜索两个串之一，使用|，表示符合其中一个条件就进行匹配，类似于OR语句。
检索column1_name包含1000或者2000的所有行

SELECT column1_name,column2_name WHERE column1_name REGEXP '1000 | 2000';

多个OR条件可并入单个正则表达式,例如’1000|2000|3000’将匹配成1000 OR 2000 OR 3000

 

实例

了解以上的正则需求后，我们就可以根据自己的需求来编写带有正则表达式的SQL语句。以下我们将列出几个小实例(表名：person_tbl )来加深我们的理解：

 

查找name字段中以’st’为开头的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

 

查找name字段中以’ok’为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

 

查找name字段中包含’mar’字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

 

查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

 

查找用户表中Email格式错误的用户记录：

SELECT * 
FROM users
WHERE email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+.[A-Z]{2,4}$'

 

^ 所匹配的字符串以后面的字符串开头

mysql> select "fonfo" REGEXP "^fo$"; -> 0（表示不匹配） 
mysql> select "fofo" REGEXP "^fo"; -> 1（表示匹配）

 

$ 所匹配的字符串以前面的字符串结尾

mysql> select "fono" REGEXP "^fono$"; -> 1（表示匹配） 
mysql> select "fono" REGEXP "^fo$"; -> 0（表示不匹配） 

 

匹配任何字符（包括新行）

mysql> select "fofo" REGEXP "^f.*"; -> 1（表示匹配） 
mysql> select "fonfo" REGEXP "^f.*"; -> 1（表示匹配）

a* 匹配任意多个a（包括空串)

mysql> select "Ban" REGEXP "^Ba*n"; -> 1（表示匹配） 
mysql> select "Baaan" REGEXP "^Ba*n"; -> 1（表示匹配） 
mysql> select "Bn" REGEXP "^Ba*n"; -> 1（表示匹配）

 

a+ 匹配任意多个a（不包括空串)

mysql> select "Ban" REGEXP "^Ba+n"; -> 1（表示匹配） 
mysql> select "Bn" REGEXP "^Ba+n"; -> 0（表示不匹配）

 

a? 匹配一个或零个a

mysql> select "Bn" REGEXP "^Ba?n"; -> 1（表示匹配）
mysql> select "Ban" REGEXP "^Ba?n"; -> 1（表示匹配）
mysql> select "Baan" REGEXP "^Ba?n"; -> 0（表示不匹配）

 

de|abc 匹配de或abc

mysql> select "pi" REGEXP "pi|apa"; -> 1（表示匹配）
mysql> select "axe" REGEXP "pi|apa"; -> 0（表示不匹配）
mysql> select "apa" REGEXP "pi|apa"; -> 1（表示匹配）
mysql> select "apa" REGEXP "^(pi|apa)$"; -> 1（表示匹配）
mysql> select "pi" REGEXP "^(pi|apa)$"; -> 1（表示匹配）
mysql> select "pix" REGEXP "^(pi|apa)$"; -> 0（表示不匹配）

 

(abc)*  匹配任意多个abc（包括空串)

mysql> select "pi" REGEXP "^(pi)*$"; -> 1（表示匹配）
mysql> select "pip" REGEXP "^(pi)*$"; -> 0（表示不匹配）
mysql> select "pipi" REGEXP "^(pi)*$"; -> 1（表示匹配）

 

匹配特殊字符
正则表达式由一些特殊的字符组成，例如.、[]、|和-等，此时要匹配它们需要使用 \\ 进行转义

\\也用来引导元字符

多数正则表达式使用单个反斜杠匹配特殊字符，mysql要求两个，mysql自己解释一个，正则表达式解释一个

 

匹配字符类
为了匹配经常使用的数字、所有字母字符、所有数字字母字符等，可以使用预定义的字符集，称为字符类(character class)。

 

匹配多个实例
可以使用正则表达式重复元字符来实现

例如：
匹配column1_name包含app或者apps的行。？表示在它前面的任何字符出现一次或者零次

SELECT column1_name,column2_name WHERE column1_name REGEXP 'apps?'

 

匹配column1_name包含连续四位任意数字的行
正则表达式一般有很多种写法，’[[:digit:]]{4}’等价于’[0-9][0-9][0-9][0-9]’

SELECT column1_name,column2_name WHERE column1_name REGEXP '[[:digit:]]{4}'

定位符
正则表达式匹配一个字符串中任意位置的文本，为了匹配特定位置的文本需要使用定位符。

例如：
匹配以数字开始的column1_name的行，如果不使用^则会在column1_name的任意位置进行匹配，中间位置有数字的行也会被检索出来，无法达到预期目的。

SELECT column1_name,column2_name WHERE column1_name REGEXP '^[0-9]';

 

^ 的双重用法
1. 在集合（用[]定义）中，用它表示否定该集合
2. 指串的开始处

另外，在集合中,用\^开始，用$结束（即[\^ xxx $]），可以使REGEXP具有和LIKE相同的作用，表示匹配整个列值

在数据库中可以在没有表的情况下用SELECT测试正则表达式，REGEXP总是返回0（不匹配）或者1（匹配）

SELECT 'hello' REGEXP '[a-zA-Z]'

返回结果：1

 

 

本文：Mysql 替换最后一个字符, SQL语句去掉表中某字段最后一个字符,MySQL 正则表达式,mysql replace last characters in string if matched