Take the 2-minute tour ×
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.. )

share|improve this question
it seems like there is no reason to have the LIKE '%12%' at all. Why not only LIKE '%125%'? –  abresas Nov 11 '11 at 15:08
add comment

6 Answers

up vote 0 down vote accepted

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.

share|improve this answer
Thank you.. guess it's going to be a long query then.. –  Sem Nov 11 '11 at 15:41
add comment

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 + '%'
share|improve this answer
add comment

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]+$' 
share|improve this answer
+1: Shame that REGEXP doesn't perform better than wildcarding the left side of a LIKE comparison. –  OMG Ponies Nov 11 '11 at 15:13
@OMGPonies Totally agree. –  Michael Berkowski Nov 11 '11 at 15:14
Sorry was making a mistake there... what would you do in this case? –  Sem Nov 11 '11 at 15:33
@Zundrium I would probably use REGEXP instead of multiple LIKE clauses, if you can compose a regex to do what you need. –  Michael Berkowski Nov 11 '11 at 21:20
add comment

in your example, yes:

FROM company_address
WHERE address_telephone LIKE '%125%'


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

share|improve this answer
add comment

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer


