February 20, 2014 at 5:30 am
I have a requirement to provide an end user with three options:
1) To obtain all the records with myField starting with myData.
2) To obtain all the records with myField ending with myData.
3) To obtain all the records with myField having myData, BUT not as per 1) or 2) above.
For example:
Record myField
----------------
1 'Hello'
2 'myDataHello'
3 'HellomyData'
4 'HellomyDataHello'
correct solutions I have so far:
1) using myField LIKE 'myData'+'%' I will retrieve record 2 from above
2) using myField LIKE '%'+'myData' I will retrieve record 3 from above
my issue:
3) using myField LIKE '%'+'myData'+'%' I will retrieve records 2,3 and 4 from above, whilst I should have only record 4
I can overcome it by adding
AND NOT myField LIKE 'myData'+'%'
AND NOT myField LIKE '%'+'myData'
but it causes me an additional maintenance headache as the script is dynamic, so I wonder if there is any better solution, please?
Also will my above approach with AND NOT significantly affect the performance, as I would assume it should?
Many thanks in advance!
February 20, 2014 at 5:42 am
performance will ALWAYS suck on this: it requires a table scan due to the LIKE operator, which would have to check every value for values that contain or end with the search term.
only the startswith LIKE can use an index, and that assumes an index exists on the column...it might not be there yet.
for #3, i got this to work, joining the table against itself with an EXISTS clause:
notice how i converted your post to easily usable data that anyone can paste into SSMS?
if you can do that in future posts, you can get answers a log quicker, an they would be tested, working examples.
DECLARE @SearchParameter varchar(30) = 'myData'
;WITH MyCTE([Record],[myField])
AS
(
SELECT '1','Hello' UNION ALL
SELECT '2','myDataHello' UNION ALL
SELECT '3','HellomyData' UNION ALL
SELECT '4','HellomyDataHello'
)
-- subquery testing exists
SELECT T1.*
FROM MyCTE T1
WHERE myField LIKE '%' + @SearchParameter
AND NOT EXISTS (SELECT 1
FROM MyCTE T2
WHERE T1.Record = T2.Record
AND T2.myField LIKE '%' + @SearchParameter + '%'
AND myField NOT LIKE '%' + @SearchParameter
)
Lowell
February 20, 2014 at 5:45 am
Hi Lowell
Thank you for the quick reply, I didn't expect a solution, but guidelines what to do.
Thanks again!
February 20, 2014 at 2:58 pm
Instead of the overhead of a self-join, try this for case #3:
WHERE
myField LIKE '_%' + @SearchParameter + '%' AND
CHARINDEX(@SearchParameter, myField) > 1 AND
RIGHT(myField, LEN(@SearchParameter)) <> @SearchParameter
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 20, 2014 at 3:25 pm
You need to adjust your wildcard usage.
1/2 is handled via the %mydata and mydata%. You can't use an anchor character but that will function. For #3, you need to include the spacers... so you need %_mydata_% to make sure there are wrapper characters to your 'middle' data.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply