Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

220
Vistas
Using Regular Expressions in MySql

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

over 4 years ago · Santiago Trujillo
1 Respuestas
Responde la pregunta

0

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

over 4 years ago · Santiago Trujillo Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda