Filter in WHERE Postgresql

Asked

Viewed 1,257 times

1

Good morning guys, next, I’m doing a standardization of some records here in the company. So there are 2 tables.

Table A(old records) columns as:

id_alvara | logradouro
1         | Rua Sete de Setembro n° 200
2         | Rua tal 300
3         | Rua de Teste n555
4         | Rua nada 2000

Table B(new records) columns as:

id_logradouro | logradouro | num
1             | Rua Tal    | 220
2             | Rua de Test| 555

What you take is the following, as I do a filter in table A to search for the exact number?

For example, in a form, the user is typing the number 220, have to return to it the correct record 1?

I used in WHERE a like( ... Where like '%200%') but it returns me any number that has 200, and it does not solve me.

As a filter and pull out all the letters?

I think I understood, I await return and thank you and who to answer! Hug

EDIT1: Highlighting the problem, sometimes the user, when he registered his address, put the number of his residence like: n°220 or N220 etc, this prevents me to use directly the 220.

postgresql filter where

  • I don’t quite understand. You are searching in table A, I go there and write: 200, you do not want to use the like pq will return all the places that have 200 in some position?

2 answers

1


I managed to solve my problem using a Translate function.

I’ll paste the SQL here to whom it matters:

SELECT a.id_alvara, a.id_alvara as numero, a.nome, a.firma, a.tipo_log||' '||a.logradouro||' '||a.complemento as endereco FROM alvara a JOIN atividade at ON a.id_atividade = at.id_atividade WHERE (translate(a.tipo_log||' '||a.logradouro||' '||a.complemento, translate(a.tipo_log||' '||a.logradouro||' '||a.complemento, '1234567890', '') , '')) = '220'

-1

The code that Voce has with 'WHERE LIKE "%220%" looks for all the elements that have 220 in the middle, this would give conflicts eventually because it could resume several records with 220 inserted in the middle and not only 220, so this should work "SELECT * FROM num WHERE num = 220" or "SELECT * FROM num WHERE num LIKE "220""

  • The problem is with SELECT in the first table.. in the second I get good.

  • Deculpe, it seems that I misunderstood the question, try then "SELECT * FROM tableThe WHERE street LIKE "%220"", this will search all the records that have the number 220 at the end

  • I had already tried, it softens but does not solve!

  • SELECT substring(STREET FROM '[0-9]+') FROM tableThis is another way to extract numbers from a sentence

  • I managed otherwise, but thanks for the answer (Y)

Browser other questions tagged postgresql filter where

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