December 9, 2015 at 1:00 pm
SQL Gurus,
I'd like to dynamically filter a couple of concatenated fields to see if they contain a mix of words.
If I was doing it manually it would look like this ...
SELECT BPID, AccountCode, PartnerName, Address, Town, County, Postcode, Country, Role1, Role2, Role3, Role4, SelfBilling, BPStatusID, SSMA_TimeStamp
FROM dbo.tblBusinessPartner
WHERE (ISNULL(PartnerName, N'') + ISNULL(Address, N'') LIKE N'%straw%') AND (ISNULL(PartnerName, N'') + ISNULL(Address, N'') LIKE N'%farm%') AND
(ISNULL(PartnerName, N'') + ISNULL(Address, N'') LIKE N'%lane%')
I'd like this to be done with a stored procedure that takes as its input parameter a comma delimited string such as 'straw,farm,lane' and for there to be an iteration of sorts to produce the same result as with the manual example above.
I have looked at the csv splitter function to see if that would work but it widens the scope rather than limits the scope of the criteria. http://www.sqlservercentral.com/articles/Tally+Table/72993/
Thank you in advance for your help with this.
December 9, 2015 at 1:11 pm
Do you want the concatenation to be able to cause a match? For example, if the PartnerName ends in "f", and the Address begins with "arm", should that cause a match on "farm"? if not, don't concatenate, check each column separately.
You should also consider using indexes with only the PartnerName and Address columns and the row key column(s) to identity matching rows, then using those IDs to pull the rest of the data columns. For a limited number of matches, that should provide much better overall performance.
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".
December 9, 2015 at 1:56 pm
I'd like this to be done with a stored procedure that takes as its input parameter a comma delimited string such as 'straw,farm,lane' and for there to be an iteration of sorts to produce the same result as with the manual example above.
I can't recreate the resultset because you did not include DDL for tblBusinessPartner.
If you want to take a comma-delimited string and return tables where the string contains any of the values in the input parameter you could do this:
DECLARE @table TABLE (stringid int identity, string varchar(100));
DECLARE @searchwords varchar(100) = 'dog,cat,fish';
INSERT @table VALUES ('dog fish'),('dog cat fish'),('fish dog cat'),('saasdfasdf!!');
-- string contains any of the words
SELECT *
FROM @table t1
WHERE EXISTS
(
SELECT stringid
FROM @table t2
CROSS APPLY dbo.delimitedsplit8k(@searchwords,',')
WHERE string LIKE '%'+item+'%'
AND t1.stringid = t2.stringid
);
If you wanted to return strings that contained all the delimited values from your input parameter you could do this:
DECLARE @table TABLE (stringid int identity, string varchar(100));
DECLARE @searchwords varchar(100) = 'dog,cat,fish';
INSERT @table VALUES ('dog fish'),('dog cat fish'),('fish dog cat'),('saasdfasdf!!');
-- string contains any of the words regardless of what order they appear
SELECT *
FROM @table t1
WHERE NOT EXISTS
(
SELECT stringid
FROM @table t2
CROSS APPLY dbo.delimitedsplit8k(@searchwords,',')
WHERE string NOT LIKE '%'+item+'%'
AND t1.stringid = t2.stringid
);
-- Itzik Ben-Gan 2001
December 9, 2015 at 2:01 pm
Also, for best performance, specify the least likely to match condition first. If one (or more) of the AND conditions are going to be false, you want SQL to discover that as soon as possible so it might be able to short-circuit the other comparisons.
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".
December 9, 2015 at 2:39 pm
Mr Burstein you are a genius, thank you so much that is exactly what I was looking for!
I've turned that into a stored procedure which works with my table and the 2 concatenated fields.
Create PROCEDURE [dbo].[GetBPData]
(
@criteria varchar(200)
)
AS
SELECT *
FROM tblBusinessPartner t1
WHERE NOT EXISTS
(
SELECT BPID
FROM tblBusinessPartner t2
CROSS APPLY dbo.delimitedsplit8k(@criteria,',')
WHERE isnull(t2.PartnerName,'')+isnull(t2.Address,'') NOT LIKE '%'+item+'%'
AND t1.BPID = t2.BPID
);
December 9, 2015 at 2:57 pm
NP. Glad that worked for you. 😀
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply