asp.net listview searching with SQL query

Question

i have a listview with search options with three fileds namecommentdescription.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 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?

Sant Roy · Answer

finally i have done this using this query.thanks to you for giving me your valuable time. create PROC [dbo].[GetDetails](@SearchName VARCHAR(200)) AS BEGIN IF ISNULL(@SearchName'') = '' BEGIN SELECT * FROM t_Organization END ELSE BEGIN DECLARE @Count INT; DECLARE @i INT = 0 WHILE (@i) BEGIN SELECT @Count = COUNT(*) FROM t_Organization WHERE (Name LIKE '%' + SUBSTRING(@SearchName1LEN(@SearchName) - @i) + '%') OR (DescriptionShort LIKE '%' + SUBSTRING(@SearchName1LEN(@SearchName) - @i) + '%') OR (DescriptionLong LIKE '%' + SUBSTRING(@SearchName1LEN(@SearchName)- @i) + '%') OR (Comment LIKE '%' + SUBSTRING(@SearchName1LEN(@SearchName) - @i) + '%') IF @Count > 0 BEGIN SELECT * FROM t_Organization WHERE (Name LIKE '%' + SUBSTRING(@SearchName1LEN(@SearchName) - @i) + '%') OR (DescriptionShort LIKE '%' + SUBSTRING(@SearchName1LEN(@SearchName) - @i) + '%') OR (DescriptionLong LIKE '%' + SUBSTRING(@SearchName1LEN(@SearchName) - @i) + '%') OR (Comment LIKE '%' + SUBSTRING(@SearchName1LEN(@SearchName) - @i) + '%') order by Name END SET @i = @i + 1 END END --DROP TABLE t_Organization END

Sant Roy · Answer

thanks for the reply.tell me where i can add the constrains keyword.please modify my SQL 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

Vishal Jadav · Answer

Hi  What you are trying to achieve is checking existence of string in table value you might need to use contains keyword in your sql statement. Thank you.