I need to query a table using REGEXP, but for some reason some regular expressions I try fail and I don't understand why.
I made a fiddle with the test case at http://www.sqlfiddle.com/#!9/e1dad3/5 , I still put the codes here just in case
CREATE TABLE IF NOT EXISTS `tabla` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Codigo` char(11) NOT NULL,
`Nombre` char(37) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2528 ;
--
-- Volcado de datos para la tabla `tabla`
--
INSERT INTO `tabla` (`id`, `Codigo`, `Nombre`) VALUES
(23, 'C -III -013', 'Nombre'),
(55, 'C -I -013', 'Nombre'),
(94, 'C -V -013', 'Nombre'),
(98, 'C -IV -013', 'Nombre'),
(279, 'C -II -013', 'Nombre'),
(551, 'LA-I -013', 'Nombre'),
(1215, 'C -VI -013', 'Nombre'),
(1449, 'AP-I -013', 'Nombre'),
(1506, 'AD- -013', 'Nombre'),
(1536, 'C -VII -013', 'Nombre'),
(1722, 'LE-I -013', 'Nombre'),
(1787, 'C -VIII-013', 'Nombre'),
(2021, 'C -IX -013', 'Nombre'),
(2264, 'C -X -013', 'Nombre'),
(2502, 'C -XI -013', 'Nombre');
SELECT * FROM `tabla` WHERE Codigo REGEXP '\s*-013\s*$';
SELECT * FROM `tabla` WHERE Codigo REGEXP 'I\s*-013\s*$';
SELECT * FROM `tabla` WHERE Codigo REGEXP '-I\s*-013\s*$';
The first query with the regular expression \s*-013\s*$
shows everything correctly, it works fine
The second does not, it I\s*-013\s*$
brings only 1 result, C -VIII-013
when in reality the ones that should match are all these:
C -III -013
C -I -013
C -II -013
LA-I -013
C -VI -013
AP-I -013
C -VII -013
C -VIII-013
C -XI -013
The third -I\s*-013\s*$
directly does not bring anything, it should bring these
C -I -013
LA-I -013
AP-I -013
LE-I -013
I tested these expressions on https://regex101.com/ and they work fine there. That's why I don't understand why in MySQL they don't produce the expected result.
Mysql does not recognize the space regex as you have it set.
You have to put [[:space:]] for the /s
So the regex should look something like this
I leave you the official mysql documentation regarding regular expressions
https://dev.mysql.com/doc/refman/5.6/en/regexp.html
The problem is that MySQL does not support the notation
\s
in the REGEX, to solve the problem there is the notation[[:space:]]
that is any white space. This notation exists for blank spaces. If there were any other case, where the backslash is needed, the double backslash should be used (eg\&
, the correct thing would be\\&
):So an example of your query would be: