regex 在MySql中使用正则表达式

qlvxas9a  于 2022-11-18  发布在  Mysql
关注(0)|答案(2)|浏览(159)

我需要写一个SQL查询,检索和匹配记录从一个表与下列;

first_name, second_name, attribute

目标是编写一个查询,该查询只匹配列attribute具有以下形式的那些记录;

<one or more arbitrary character>%<first name>_<second name>%<zero or more arbitrary characters>

需要注意的是,即使是字母大小写也与first_namesecond_name匹配。

first_name  second_name attribute
Vicenta      Kravitz    0%Vicenta_Kravitz%
Shayne     Dahlquist    0R0V331K8Q7ypBi4Az3B6Nm0jCqUk%Shayne_Dahlquist%46E3O0u7t7
 Mikel       Kravitz    PBX86iw1Ied87Z9OarE6sdSLdt%Mikel_Kravitz%W73XOY9YaOgi060r2x12D2EmD

正如您所看到的,first_name和last_name中字母的大小写也是匹配的。

SELECT first_name,
       second_name,
       attribute
FROM table
WHERE attribute REGEXP  '^.+ CONCAT('%',binary(first_name),'_',binary(last_name),'%').*'
ORDER BY attribute;

由于大小写匹配是一个要求,我觉得binary()函数可以帮助。

ERROR 1064 (42000) at line 35: You have an error in your SQL syntax; check the manual that corresponds to 
  your MySQL server version for the right syntax to use near '_',binary(last_name),'%').*'
    ORDER BY attribute; 
END' at line 10

看手册没有太大帮助。我能得到一些反馈吗?这里可能出了什么问题?谢谢

oug3syen

oug3syen1#

你必须像这样把孔边的弦连接起来
第一次
小提琴

e7arh2l6

e7arh2l62#

SELECT first_name, second_name, attribute FROM users
    WHERE attribute REGEXP CONCAT('^.+%',first_name,'_',second_name,'%.*') COLLATE utf8_bin
ORDER BY attribute;

SELECT first_name, second_name, attribute FROM users
    WHERE attribute REGEXP CONCAT('^.{1,}%',first_name,'_',second_name,'%.{0,}') COLLATE utf8_bin
ORDER BY attribute;

SELECT first_name, second_name, attribute FROM users
    WHERE attribute LIKE binary CONCAT('_%\%',first_name,'\_',second_name,'\%%')
ORDER BY attribute;

相关问题