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
