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

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

 

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

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(表示匹配)

 

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

\\也用来引导元字符

元字符 说明
\f 换页
\n 换行
\r 回车
\t 制表
\v 纵向制表

为了匹配反斜杠(\)字符本身,需要使用\\\。

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

 

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

说明
[:alnum:] 任意字母和数字(同[0-9a-zA-Z])
[:alpha:] 任意字符(同[a-zA-Z])
[:blank:] 空格和制表符(同[\\t])
[:cntrl:] ASCII控制字符(ASCII 0到31和127)
[:digit:] 任意数字(同[0-9])
[:graph:] 与[:print:]相同,不包含空格)
[:lower:] 任意小写字母(同[a-z])
[:print:] 任意可打印字符)
[:punct:] 既不在[:alnum:]也不在[:cntrl:]中的任意字符)
[:space:] 包括空格在内的任意空白字符,同[\\f\\n\\r\\t\\v])
[:xdigit:] 任意十六进制数字(同[0-9a-fA-F])

 

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

重复元字符 说明
* 0个或多个匹配
+ 一个多多个匹配,等价于{1,}
? 0个或一个匹配,等价于{0,1}
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围(m不超过255)

例如:
匹配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

Leave a Reply