Filter in WHERE Postgresql

1

Good morning folks, 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 do you get is the following, how do I make a filter in table A to search for the exact number?

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

I used the WHERE a like (... where like '% 200%') but this returns any number that has 200, and that does not solve it.

How do I filter and shoot all the letters?

I think it gave to understand, I wait for return and thank and who to answer! Hugs

EDIT1: Highlighting the problem, sometimes the user, when he registered his address, put his residence number like this: n ° 220 or n220 etc, this prevents me from using the 220 directly.

    
asked by anonymous 06.07.2017 / 15:11

2 answers

1

I was able to solve my problem using a translate function.

I'll paste the SQL here for whoever interests:

  

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'

    
06.07.2017 / 15:58
-1

The code that you have with 'WHERE LIKE'% 220% 'looks for all the elements that have 220 in the middle, this would give conflicts eventually, since it could retrieve several registers with 220 inserted in the middle and not only the 220, to function     "SELECT * FROM num num WHERE = 220" or     "SELECT * FROM a WHERE in a LIKE" 220 ""

    
06.07.2017 / 15:37