June 20, 2015 at 12:09 am
DECLARE @search VARCHAR(10);
SET @search = 'dog,COW';
WITH T(C)
AS
(
SELECT 'Cat,Dog,Sparrow,Trout,Cow,Seahorse'
UNION ALL
SELECT 'Cat,Dog,Sparrow,Trout,Cow,Seahorse'
)
SELECT *
FROM T
WHERE C LIKE '%' + @search + '%'
I have a string in a cell 'Cat,Dog,Sparrow,Trout,Cow,Seahorse'
want to search any of the two names from the string.
Search string:'cat,cow'
Result:no result (not working),[font="Tahoma"][/font]
Search string:'cat,dog'
result :given string(working fine )
June 20, 2015 at 12:55 am
Before I get into possible solutions, if you're just starting on this database, I would advise you to reconsider your design. Basically you're storing more than one fact in a single column in your database. If you use LIKE '%' + @searchString + '%' to do your search, you're going to force a table scan every single time you do it. In other words, you're asking the database engine to read the entire table every time you ask for records, so performance is going to be awful.
If you did something like this:
CREATE TABLE ParentTable (
ParentID INT IDENTITY(1,1),
ParentName VARCHAR(20)
CONSTRAINT pkParentTable PRIMARY KEY (ParentID),
CONSTRAINT uqParentName UNIQUE (ParentName));
and then added the animals as child records of the Parent table...
CREATE TABLE Animal (
AnimalName VARCHAR(20)
,ParentID INT
CONSTRAINT pkAnimal PRIMARY KEY (AnimalName),
CONSTRAINT fkParentID FOREIGN KEY (ParentID) REFERENCES ParentTable(ParentID));
then you could search for individual animals and use a join to filter. Otherwise, you're going to have a horrible time if you need to update your table or filter it.
June 20, 2015 at 9:40 am
+1 on this being a VERY BAD design...
If for some reason you aren't able to rework the design, something like the following should work...
DECLARE @search VARCHAR(10);
SET @search = 'dog,COW';
WITH T(C)
AS
(
SELECT 'Cat,Dog,Sparrow,Trout,Cow,Seahorse'
UNION ALL
SELECT 'Cat,Dog,Sparrow,Trout,Cow,Seahorse'
), Add_ID AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID,
T.C
FROM T
)
SELECT
MIN(ai.C) AS C
FROM Add_ID ai
CROSS APPLY dbo.SplitCSV8K(ai.C, ',') sc
WHERE
sc.Item IN (SELECT Item FROM dbo.SplitCSV8K(@search, ','))
GROUP BY ai.ID
Note... The string splitter function is a renamed copy of Jeff Moden's splitter, which can be found here...
http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]
HTH,
Jason
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply