Using Regular Expressions in MySql

Question

I need to write a sql query which retrieves and matches records from a table with following columns;

first_name, second_name, attribute

The goal is to write a query, which matches only those records where the column attribute is of the following form;

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

It should be noted that even the letter cases match for first_name and second_name. Sample output should look like;

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

As you can see, the cases for the letters in first_name and last_name also match. Here is my attempt;

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

Since case matching is a requirement, I feel binary() function can help. But I am getting following syntax error;

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

Looking at the manual is not helping a lot. Can I get some feedback what may be going wrong here?thanks

Parse the "attribute" before storing into the table. This may lead to multiple rows going into the table, but the SQL will be much simpler. — Rick James
– Rick James, Commented Aug 9, 2020 at 16:36

nbk · Accepted Answer · 2020-07-31 18:34:22Z

You have to concat the hole reg string like

CREATE TABLE Table1 (`first_name` varchar(7), `second_name` varchar(9), `attribute` varchar(66)) ; INSERT INTO Table1 (`first_name`, `second_name`, `attribute`) VALUES ('Vicenta', 'Kravitz', '0%Vicenta_Kravitz%'), ('Shayne', 'Dahlquist', '0R0V331K8Q7ypBi4Az3B6Nm0jCqUk%Shayne_Dahlquist%46E3O0u7t7'), ('Mikel', 'Kravitz', 'PBX86iw1Ied87Z9OarE6sdSLdt%Mikel_Kravitz%W73XOY9YaOgi060r2x12D2EmD') ;

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

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

db<>fiddle here

Appreciate your help. thanks for sharing the dbfiddle link also.

James Risner · Accepted Answer · 2022-11-08 13:06:05Z

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

or

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

or

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

Collectives™ on Stack Overflow

Using Regular Expressions in MySql

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related