i have a listview with search options with three fileds name,comment,description.i have implement the search options using this query.
SELECT OrganizationID,
Name,
DescriptionShort,
DescriptionLong, Comment,
DateUpdated,
FROM t_Organization
where t_Organization.Name like ''''%''''+@name+''''%''''
or t_Organization.DescriptionShort like ''''%''''+@name+''''%''''
or t_Organization.DescriptionLong like ''''%''''+@name+''''%''''
or t_Organization.Comment like ''''%''''+@name+''''%''''
ORDER BY Name
it works perfectly.but if when i add a extra string then no records are fetching.
like ''''player'''' search perfectly
like ''''play'''' search perfectly
like ''''pla'''' search perfectly
like ''''pl'''' search perfectly
like ''''p'''' search perfectly
but ''''Players'''' , not work
like ''''playerw'''' not work
like ''''playing'''' not work.
means when we add a extra string it does not work .
.how can i do this?
i need all.
search will happen because play is common..
please help me....how can i do this?
Here I have created sample that will help you out.
SQL
-- EXEC GetDetails ''''David''''
CREATE PROC GetDetails(@SearchName VARCHAR(200))
AS
BEGIN
CREATE TABLE #t_Organization(OrganizationID INT,Name VARCHAR(100),DescriptionShort VARCHAR(100),DescriptionLong VARCHAR(100),Comment VARCHAR(100),DateUpdated DATETIME)
INSERT INTO #t_Organization VALUES(1,''''David Morkel'''',''''Player'''',''''Player'''',''''Comment1'''',''''2015-02-02'''')
INSERT INTO #t_Organization VALUES(2,''''Kevin'''',''''Developer'''',''''Developer'''',''''Comment2'''',''''2015-02-03'''')
INSERT INTO #t_Organization VALUES(3,''''Peter'''',''''Engineer'''',''''Engineer'''',''''Comment3'''',''''2015-02-04'''')
IF ISNULL(@SearchName,'''''''') = ''''''''
BEGIN
SELECT * FROM #t_Organization
END
ELSE
BEGIN
DECLARE @Count INT;
DECLARE @i INT = 0
WHILE (@i) < LEN(@SearchName)
BEGIN
SELECT @Count = COUNT(*) FROM #t_Organization
WHERE (Name LIKE ''''%'''' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + ''''%'''')
OR (DescriptionShort LIKE ''''%'''' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + ''''%'''')
OR (DescriptionLong LIKE ''''%'''' + SUBSTRING(@SearchName,1,LEN(@SearchName)- @i) + ''''%'''')
OR (Comment LIKE ''''%'''' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + ''''%'''')
IF @Count > 0
BEGIN
SELECT * FROM #t_Organization
WHERE (Name LIKE ''''%'''' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + ''''%'''')
OR (DescriptionShort LIKE ''''%'''' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + ''''%'''')
OR (DescriptionLong LIKE ''''%'''' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + ''''%'''')
OR (Comment LIKE ''''%'''' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + ''''%'''')
BREAK;
END
SET @i = @i + 1
END
END
DROP TABLE #t_Organization
END
Example
