mysql 匹配指定字符结尾

矫情吗；* 2024-03-23 13:04 62阅读 0赞

学习了《[mysql正则表达式][mysql]》，接下去来实际应用。

## 匹配指定字符结尾 ##

#### 情况： ####

![417e97f043884a4d8a231fce5a860aa1.png][]

数据准备：

A,B,C；A,B,C,D；A,B,D；A,B,C,D,H；A,B,E,G,D,H；A,B,E；A,B,E,G；A,B,F；A,B,F,H；

SELECT 'A,B,C' AS path UNION ALL 
    SELECT 'A,B,C,D' AS path UNION ALL 
    SELECT 'A,B,D'  AS path UNION ALL
    SELECT 'A,B,C,D,H'  AS path UNION ALL
    SELECT 'A,B,E,G,D,H'  AS path UNION ALL
    SELECT 'A,B,E'  AS path UNION ALL 
    SELECT 'A,B,E,G'  AS path UNION ALL 
    SELECT 'A,B,F'  AS path UNION ALL
    SELECT 'A,B,F,H'  AS path

![9176b0cf5a6a4a8aae6a6f5f60fe0474.png][]

### 查询以B,D或B,C结尾的。 ###

#### like '%speacialWord' ####

SELECT * FROM (
    SELECT 'A,B,C' AS path UNION ALL
    SELECT 'A,B,C,D' AS path UNION ALL
    SELECT 'A,B,D'  AS path UNION ALL
    SELECT 'A,B,C,D,H'  AS path UNION ALL
    SELECT 'A,B,E,G,D,H'  AS path UNION ALL
    SELECT 'A,B,E'  AS path UNION ALL
    SELECT 'A,B,E,G'  AS path UNION ALL
    SELECT 'A,B,F'  AS path UNION ALL
    SELECT 'A,B,F,H'  AS path ) AS temp
    WHERE path LIKE '%B,C' OR path LIKE '%B,D' ;

#### REGEXP 'specialWord$' ####

SELECT * FROM (
    SELECT 'A,B,C' AS path UNION ALL
    SELECT 'A,B,C,D' AS path UNION ALL
    SELECT 'A,B,D'  AS path UNION AL
    SELECT 'A,B,C,D,H'  AS path UNION AL
    SELECT 'A,B,E,G,D,H'  AS path UNION AL
    SELECT 'A,B,E'  AS path UNION AL
    SELECT 'A,B,E,G'  AS path UNION AL
    SELECT 'A,B,F'  AS path UNION AL
    SELECT 'A,B,F,H'  AS path ) AS temp
    WHERE path REGEXP 'B,(C|D)$' ;

多个的，用正则更方便些；

## 不是以什么开头 ##

-- 不是以数字开头
    SELECT * FROM x_table WHERE type NOT REGEXP '^[0-9]' ;
    
    -- 不是以字母开头
    SELECT * FROM x_table WHERE type NOT REGEXP '^[a-zA-Z]' ;
    
    
    -- 不是数字和特殊字符开头
    SELECT * FROM x_table WHERE type REGEXP '^[@#$%&0-9]' ;

### 总结： ###

匹配特定字符结尾的，简单的，可以用like去处理。多个的的，适合用正则的方式去写，比较简便些。 也可以根据实际的情况，用其它方式去处理。

[mysql]: https://blog.csdn.net/qq_35461948/article/details/130181191
[417e97f043884a4d8a231fce5a860aa1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/860d7b11cad642bf99309664fcb91e59.png
[9176b0cf5a6a4a8aae6a6f5f60fe0474.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/8bbddcbb6c5942619b4d34dbdd2dd6b0.png