Using Regular Expressions in MySql

Issue

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

Solution

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

Answered By – nbk

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0