CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2005
sairam02
sairam02

Reputation: 33

Is there any alternative of 'like' operators in sql - for searching data

I need some help here, I am fetching data dependent upon the conditions

Here I am using like operators, where client may use search with any word thus I have the below logic but here retrieval time is too high (it's taking 4mins) to get the data which is too slow.

I did all indexing to table but still can't optimize the query.

select * from authors where (address like '% Walmart %' OR address like 'Walmart %' OR address like '% Walmart' OR address like '% Walmart.com %' OR address like 'Walmart.com %' OR address like '% Walmart.com' OR address like '% Walmarts %' OR address like 'Walmarts %' OR address like '% Walmarts' OR address like '% Walmarts.com %' OR address like 'Walmarts.com %' OR address like '% Walmarts.com')

-- Result is 1 Rows

if i follow this logic

select * from authors where address like '%Walmart%'

-- Result is 44 rows - it is fetching all rows

But I need to get only that one row

Upvotes: 1

Views: 12604

Answers (6)

Rai Muhammad Ehtisham
Rai Muhammad Ehtisham

Reputation: 306

If you're using LIKE in your QUERY please use double %% to wrap the value to be filtered upon. This is the best solution. Please vote if you like the solution. For example in my case this was concatenated with the main query

filter_value = "'%%"+filter_value+"%%'"

Upvotes: 0

Marlin Pierce
Marlin Pierce

Reputation: 10099

As others have said, all you need is

select * from authors where address like '%' + @word + '%'

This might not speed things up much, if the optimizer figured out what we are telling you. (also the index won't help, and it is a good suggestion by others to use full-text searching).

In case you don't get why the query above is all you need, let's look at your query:

select * from authors
where address like @word or address like '% '+ @word
 or address like @word+' %' or address like '%'+@word+'%'
 or address like '% '+@word+' %' or address like @words
 or address like '% '+@words or address like @words+' %'
 or address like '%'+@words+'%' or address like '% '+@words+' %'
 or address like @wordcom or address like '% '+@wordcom
 or address like @wordcom+' %' or address like '%'+@wordcom+'%'
 or address like '% '+@wordcom+' %' or address like @wordscom
 or address like '% '+@wordscom or address like @wordscom+' %'
 or address like '% '+@wordscom+' %'

You do have a "like @word + '%'", so notice that if a record matches "like @word" it would also match "like @word + '%'". So @word won't add any more results. Because of the "or", the results will be the same wether @word is trying to match, or you just have results from @word+'%'.

Applying that logic, you query could be simplified to:

select * from authors
where address like @word+' %' or address like '%'+@word+'%'
 or address like @words+' %' or address like '%'+@words+'%'
 or address like @wordcom+' %' or address like '%'+@wordcom+'%'
 or address like @wordscom+' %' or address like '% '+@wordscom+' %'

Likewise, @word doesn't add anything that '%'+@word didn't already include, which thus simplifies the query to:

select * from authors
where address like '%'+@word+'%'
 or address like '%'+@words+'%'
 or address like '%'+@wordcom+'%'
 or address like '% '+@wordscom+' %'

Further, anything found by '%'+@word+'s%', '%'+@word+'.com%' or , '%'+@word+'s.com%' would be found by '%'+@word+'%'.

Upvotes: 0

Mario S
Mario S

Reputation: 11955

I believe this is the only thing you need in the where clause, it should cover everything containing that word:

select * from authors
where address like '%' + @word + '%'

You are actually already doing this on the second line of the where clause:

or like '%'+@word+'%'

And that covers everything, making all other excessive. Removing those will probably speed it up a little:

declare @title varchar(20)

set @title = 'coll' set @title = LTRIM(rtrim(@title))

select * from authors
where address like '%'+@title+'%'

Upvotes: 3

Farfarak
Farfarak

Reputation: 1527

If you would like to get proper performance on text columns you will need to use full-text search.

http://msdn.microsoft.com/en-us/library/ms142571.aspx

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 135181

A wildcard search like that won't be helped by an index, try looking at fulltext search instead

This will utilize an index, it will probably do a seek

address like @wordcom+' %

This will not utilize an index, it has to scan the whole index

address like '% '+@wordcom+' %'

Like mentioned above, use fulltext seach instead

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48522

It's quite likely indexing won't help. In many cases, when you use the Like statement, an index cannot be used. The type of searching you are allowing is highly inefficient. More than likely, this query will scan the address table. If the table has a sizeable number of rows in it, this will take a long time, as you're seeing.

You might be better off looking into full-text searching.

Upvotes: 2

Related Questions