SQL Like without considering the order of the parameters

Asked

Viewed 345 times

3

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

Example: Table A has a record with the ENDERECO field with the value "RUA FULANO DE TAL 67 401" and in Table B, the ENDERECO field has "RUA 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 the parameters in the informed order.

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

sql parameters

  • What you’re trying to do is extremely difficult. There are companies that market (and it is not cheap) routines for normalizing addresses, which take equal addresses, but written differently (abbreviations, position of number, complement, etc.) and transform into a single model, normalized, which can be compared by equality, or by percentage of similarity. In short, it is not a simple implementation. The solution given by Bacco, based on the simple algorithm you are implementing, will work, but do not expect very satisfactory results!

1 answer

2

Basically just sort out 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 I explain how to dynamically generate this type of query:

How to search for multiple order independent terms?

To dynamically generate this type of query, the logic is always the same: separate by words, add field LIKE '%palavra%' for each one, united by AND (or by OR if you want to search for any of the terms). Thing of half a dozen lines in most languages.

Just don’t forget to sanitize the strings to avoid SQL injection (this is independent of the technique used, the original code already suffers from this vulnerability).

In case you REALLY need to do this natively (in many cases it doesn’t justify), you need to see if said DB has the column feature with support for Full Text:

https://en.wikipedia.org/wiki/Full_text_search

I have already said that for most cases, the solution to query Mounted is simpler and practical, and does not require more complex knowledge. Full-text search already requires rethinking a little on the architecture of the application.

  • Bacco, thanks for the answer. It was great, but not in answer and I explain why:

  • 1

    I answered considering everything that is in the body of the question.

  • In fact the text "RUA FULANO DE TAL 67 401" is saved in the database in the ENDERECO field of table A. I use it in Select giving a Replace replacing the spaces by %. I cannot then mount SQL passing a parameter for each word or term to be searched.

  • 1

    This way it doesn’t work. As I said, you need to do the LIKE dynamically (there are other ways, but all more complex, I believe). If you use some language for replace, you could do the same by creating the LIKE, right? Not that it’s impossible to do with SQL, but it’s an unnecessary complication if you’re using some extra language.

  • The example I passed on the link is PHP, but the logic can easily be adapted to any language. The link is mere reference. The logic is always the same: separate by words, add campo LIKE '%palavra%' for each one, united by AND. Something of 4 or 5 lines in most languages, a little more if you want to do some extra sanitization.

Browser other questions tagged sql parameters

You are not signed in. Login or sign up in order to post.