CODEWITHSUNDEEP
mysqlsql
Sem
Sem

Reputation: 4649

SQL with multiple LIKEs over 1 variable

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.. )

Upvotes: 1

Views: 725

Answers (6)

Steve Henderson
Steve Henderson

Reputation: 514

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 + '%'

Upvotes: 3

Gustav Bertram
Gustav Bertram

Reputation: 14901

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

Upvotes: 0

JHolyhead
JHolyhead

Reputation: 984

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

Upvotes: 0

bpgergo
bpgergo

Reputation: 16037

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

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270609

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]+$'

Upvotes: 2

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

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.

Upvotes: 0

Related Questions