Question 381290 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Query that returns all words ignoring one

I have this table with the next records:

CREATE TABLE `t_vias` (
`id` int(11) NOT NULL,
`cpro` varchar(2) NOT NULL,
`cmum` varchar(3) NOT NULL,
`cpos` varchar(5) NOT NULL,
`mun` varchar(75) NOT NULL,
`via` varchar(100) NOT NULL);
 
     
INSERT INTO t_vias VALUES
("6021","28","079","28016","MADRID","GOYA, FRANCISCO DE"),
("6022","28","079","28016","MADRID","FRANCISCO GOYA"),
("6896","28","079","28001","MADRID","GOYA, DE"),
("6897","28","079","28001","MADRID","GOYA (DE)"),
("6900","28","079","28001","MADRID","CALLE GOYA"),
("6901","28","079","28001","MADRID","CALLE DE GOYA");

I receive value "CALLE DE GOYA" from a php variable, and i include in my query the variable value:

SELECT * FROM `t_vias` WHERE via ='CALLE DE GOYA';

The result shows only one record:

id  cpro    cmum    cpos    mun     via     
6901    28  079     28001   MADRID  CALLE DE GOYA

Since here, is ok. But if i want show all register with "GOYA" word (remove CALLE, DE, etc):

SELECT * FROM `t_vias` WHERE via ='%GOYA%'; 
 id     cpro    cmum    cpos    mun     via     
6021    28  079     28016   MADRID  GOYA, FRANCISCO DE
6022    28  079     28016   MADRID  FRANCISCO GOYA
6896    28  079     28001   MADRID  GOYA, DE
6897    28  079     28001   MADRID  GOYA (DE)
6900    28  079     28001   MADRID  CALLE GOYA
6901    28  079     28001   MADRID  CALLE DE GOYA

It’s ok, return all entries with GOYA.

Trouble is if the value of the variable cames as this: ‘DE GOYA’

Then the query:

SELECT * FROM `t_vias` WHERE via LIKE '%DE GOYA%'; 

id  cpro    cmum    cpos    mun     via     
6901    28  079     28001   MADRID  CALLE DE GOYA

Only return one record. How can i search variable value without DE, (DE) if it contains it?

Tags:

2

Answers


  1. 0

    I would suggest going to regex

    SELECT * FROM `t_vias` WHERE via regexp "GOYA";
    Login or Signup to reply.
  2. 0 
    SELECT * FROM `t_vias` WHERE via LIKE CONCAT('%',REPLACE('DE GOYA', 'DE ', ''), '%');

    How about this? You can replace ‘DE GOYA’ with ‘GOYA’ or ‘GOYA DE’. This will remove DE from DE GOYA -> GOYA and matches that to all records (So it will find GOYA, DE GOYA, GOYA DE, DEGOYADE, GOYADE, etc, etc.

    If you need to replace more than just ‘DE ‘, you could look into REGEXP_REPLACE.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search