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

1

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 Jul 31, 2020 at 18:34

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jay Over a year ago

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

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

0

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;

edited Nov 8, 2022 at 13:06

James Risner

6,09711 gold badges33 silver badges60 bronze badges

answered Nov 4, 2022 at 8:48

Al Moonsur

11 bronze badge

Collectives™ on Stack Overflow

Using Regular Expressions in MySql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related