SQL with multiple LIKEs over 1 variable

Question

SELECT *
FROM company_address
WHERE address_telephone LIKE '%12%'
AND address_telephone LIKE '%45%' (edit)

Short question: Is there a way to only use the column name once?

(edit: It was an example, sorry for that.. )

Answer 1

If you really want to do it in one search, you can put your search criteria into a table variable and do a wildcard join on that:

DECLARE @t table (s varchar(20))
insert into @t values('12')
insert into @t values('45')

select ca.* from company_address ca inner join
@t t on ca.address_telephone like '%' + t.s + '%'

Answer 2

Your clause is faulty. address_telephone cannot be LIKE '%12%' without also being LIKE '%125%' so only the second of them is necessary anyway.

If this is only an example case and you didn't actually intend that WHERE logic, REGEXP might work for your situation:

WHERE address_telephone REGEXP '^.*125[0-9]+$' 

Answer 3

Short answer: No

Side note: I do not know your business rules for the query, but it looks like you might want to be using OR instead of AND.

Long answer: If it was an equality, you could use IN. However, since you are using LIKE, you have to specify each LIKE condition.

Answer 4

in your example, yes:

SELECT *
FROM company_address
WHERE address_telephone LIKE '%125%'

explanation

if address_telephone LIKE '%125%' is true then address_telephone LIKE '%12%' must be true as well, so there is no need to add it to the query

Answer 5

You can normally use wildcards etc to combine multiple likes into one statement. See below for a trivial example

declare @test varchar(50)    
set @test = '123456789'    
select 'true' where @test like '123%456%9'

Edit: That returns 'true', by the way

Answer 6

Maybe you're looking for a Levenshtein distance function? It should allow you to do a fuzzy search. There's one here.