CODEWITHSUNDEEP
sql
w0051977
w0051977

Reputation: 15807

Variable in LIKE clause

I have some data in a table called Addresses:

HouseNumber Address
32          32 The Street
1           2 The Way
4           4 The Street
5           5 The Street

I am trying to find the House Numbers that are incorrect.
I thought the query below would return '2 The Way' but it does not.

select Address
FROM   Addresses
WHERE  HouseNumber NOT LIKE '%' + Address + '%'

Is there any easy way to do this like in the query above, or do I have to write a stored procedure?

Upvotes: 0

Views: 65

Answers (4)

Raad
Raad

Reputation: 4648

You need to extract the house number from the address, and check it against the house number column (not tested):

select Address
from   Addresses
where  cast(Housenumber as int) != cast(left(Address, instr(Address, " ")-1) as int)

This assumes the house number is always at the beginning of the address, and there is a space between the house number and the rest of the address.

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You must swap Address and HouseNumber. Also, because your Address starts with HouseNumber you can make your query mode correct and also sragable.

select Address
FROM   Addresses
WHERE  Address NOT LIKE HouseNumber + ' %'

Upvotes: 1

Aditi
Aditi

Reputation: 1188

Maybe you should try it the other way around

select Address FROM Addresses WHERE Address NOT LIKE '% ' + HouseNumber + ' %'

This way it will try to match '1' (housenumber) with '2 the way'. Rather than the other way around.

Upvotes: 0

Andrey Gordeev
Andrey Gordeev

Reputation: 32459

Shouldn't it be:

select Address FROM Addresses WHERE Address NOT LIKE '%' + HouseNumber + '%'

??

Upvotes: 0

Related Questions