SQL Server - Help! Tricky Query

dteshome used Ask the Experts™
I have the following query.  I replaced the UDF call because the use of "...LIKE..." is much faster - but that is not the issue;

The problem is with the AND clause where there is '**'
The column m.Keyword holds comma separated keywords.  I need to return, say, the keyword 'pool'
but the query returns records with the following format"
pool (where the keyword is at the start of the string)  -- okay
, pool  -- okay
View, pool, fixer  -- okay
, Room for pool  -- not okay

How do I ensure that I get what I expect?

Thank you.

select TOP 601 a.property_type,a.mlsnum,a.status,dbo.fn_sort_status(a.status), m.keyword -- 559 in 0 secs
from mls_unified_svo_tbl a (nolock)
LEFT OUTER JOIN mls_unified_mvo_svo_tbl m (nolock) on a.mlsnum = m.mlsnum
LEFT OUTER JOIN photos_exist b (nolock) on a.mlsnum = b.mlsnum
LEFT OUTER JOIN virtual_tours c (nolock) on a.mlsnum = c.mlsnum
where (((a.lp BETWEEN 0 AND 999000)) AND ((a.status IN (5))))
AND (a.br >= 3) AND (a.ba >= 2) AND (a.sf >= 1200)
AND (a.property_type = 0)
**AND (m.keyword like  '%Bank Owned%' OR m.keyword like '%Builder/Developer%' OR m.keyword like '%Fixer%' OR m.keyword like '%pool%' OR m.keyword like '%Beverly Hills Adjacent%' OR m.keyword like '%Turnkey%')
--AND (dbo.fn_mvo_searchall_rtrn_bln_DT('Bank Owned,Builder/Developer,Fixer,pool,Beverly Hills Adjacent,Turnkey',m.keyword)  = 1)
Room for Pool,Room for Tennis Court,Tennis Court,Yard
Court Approval,Foreclosure,Room for Pool,Unfurnished,Vacant,Yard
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
Most Valuable Expert 2011
There is this method, though it is less efficient due to formula on the keyword column, but works:
;with cte(KEYWORD) as (
select 'Turnkey'
union select 'Move-in,Pool,Turnkey,Unfurnished,Yard'
union select 'Pool'
union select 'Move-in,Pool,Turnkey,Yard'
union select 'Room for Pool,Room for Tennis Court,Tennis Court,Yard'
union select 'Granite,Move-in,Pool,Remodeled,Turnkey,Yard'
union select 'Court Approval,Foreclosure,Room for Pool,Unfurnished,Vacant,Yard'
select keyword
from cte
where ','+keyword+',' like '%,pool,%';

Open in new window


Thank you, MWVISA1. This works!

One more question.
How would you characterize the performance gain in a report? (code used to run in 24 secs with UDF, with ...LIKE ... it runs in 760 ms)
Would you say that the query runs 2300% faster, (from 24 sec to 1 sec)
or that there is a performance gain of 96%? (oldTime - NewTime)/OldTime * 100%)
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Probably the latter.  Most times in optimizing queries, you start with initial value of 24 seconds in your case and associate that to cost of each component of the query.  Moving down to 1 second, thus, eliminated 96% of the cost.  If you are promoting how much you improved the query, it might be nice to say I improved the process 2300%.  That sounds spectacular and works even if the original was 0.024 milliseconds and you optimized to 0.001.  The magnitude of the change as a percentage is still the same and sounds really good. *smile*


Thank you so much, MWVISA1.

I will use the former :)  for effect!


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial