mysql 匹配指定字符结尾

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

匹配指定字符结尾

情况：

 数据准备：

   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

查询以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去处理。多个的的，适合用正则的方式去写，比较简便些。 也可以根据实际的情况，用其它方式去处理。