SQL Like without considering the order of the parameters

Navigation
2

I need to make a comparison between two tables to find out if the address of table A is present in the address of table B.

Example: Table A has a record with the ENDERECO field with the value "STREET FULANO DE TAL 67 401" and in table B, the ENDERECO field has "STREET FULANO DE TAL, AP 401, 67". That is, it contains everything I seek, but in a different order. SQL 

SELECT * FROM TABELA_B WHERE ENDERECO LIKE '%RUA%FULANO%DE%TAL%67%401%'

does not return anything, because SELECT looks for parameters in the reported order.

Now my question: Is there a way to search for all parameters, regardless of the order they were entered?

    
asked by anonymous 18.10.2016 / 23:01

1 answer

2

Basically, it's only necessary to separate the conditions: 

SELECT
   *
FROM
   TABELA_B
WHERE
   ENDERECO LIKE '%RUA%'
   AND ENDERECO LIKE '%FULANO%'
   AND ENDERECO LIKE '%DE%'
   AND ENDERECO LIKE '%TAL%'
   AND ENDERECO LIKE '%67%'
   AND ENDERECO LIKE '%401%'

Here's how to dynamically generate this kind of query :

  

Search for multiple php sql terms

To dynamically generate this type of query , the logic is always the same: separate by words, add field LIKE '%palavra%' to each, joined by AND . Thing of half a dozen lines in most languages.

Just do not forget to sanitize strings to avoid SQL injection (this does not depend on the technique used, the original code already suffers from this vulnerability).

If you REALLY need to do this natively (in many cases it does not justify), you need to see if the DB has the columns feature with Full Text support:

  

link

I already added that for most of the cases, the mounted query solution is simpler and more practical, and does not require more complex knowledge. Full-text search already requires a little rethink in the application architecture.

    
18.10.2016 / 23:18
Pass data captured from a Python code to Shell Script What is the best way to use Node dependencies?