BEGIN --Key Words
DECLARE @String as VARCHAR(500)
SET @String = 'Kansas,64108'
END
BEGIN --Sample Data
DECLARE @UserMaster TABLE (UserName VARCHAR(50), Address VARCHAR(50), City VARCHAR(50), Zip VARCHAR(50))
INSERT INTO @UserMaster VALUES ('aa', '1810 Cherry Street', 'Kansas City', '64108')
INSERT INTO @UserMaster VALUES ('bb', '1810 Cherry Street', 'New york City', '64119')
INSERT INTO @UserMaster VALUES ('cc', '1810 Cherry Street', 'Kansas City', '64108')
INSERT INTO @UserMaster VALUES ('dd', '1810 Cherry Street', 'New york City', '64119')
INSERT INTO @UserMaster VALUES ('ee', '1810 Cherry Street', 'Kansas City', '64108')
INSERT INTO @UserMaster VALUES ('ff', '1810 Cherry Street', 'New york City', '64119')
END
BEGIN --Return Result
DECLARE @XML XML
SET @XML ='<Root><SearchKey>' + REPLACE( @String, ',', '</SearchKey><SearchKey>') + '</SearchKey></Root>'
SELECT UserMaster.*, SearchInfo.SearchKey
FROM @UserMaster AS UserMaster
INNER JOIN (
SELECT x.Name.value('.', 'VARCHAR(MAX)') AS SearchKey
FROM @XML.nodes('/Root/SearchKey')x(Name)
) SearchInfo ON UserMaster.City Like '%' + SearchInfo.SearchKey + '%'
OR UserMaster.Zip Like '%' + SearchInfo.SearchKey + '%'
OR UserMaster.Address Like '%' + SearchInfo.SearchKey + '%'
END
Friday, April 2, 2010
Search Mutipal Keyword in Multipal Field
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment