query to search related data in database

Question

i will use like this: declare @SearchString varchar (200) select @ from tbl_Products WHERE ProductName Like '%'+@SearchString+'%' or ProductCode Like '%'+@SearchString+'%' i will get result like : products name: Blue New Product and search time : input text like: new blue then it not find any result i want when input this keyword then i got this product name so please solve it.. thanks..

ketan borsdiya · Answer

Hi Devendra First of all create below function CREATE FUNCTION [dbo].[fn_Split](@text varchar(8000) @delimiter varchar(20)) RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY value varchar(8000) ) AS BEGIN DECLARE @index int SET @index = -1 WHILE (LEN(@text) > 0) BEGIN SET @index = CHARINDEX(@delimiter  @text) IF (@index = 0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAK END IF (@index > 1) BEGIN INSERT INTO @Strings VALUES (LEFT(@text @index - 1)) SET @text = RIGHT(@text (LEN(@text) - @index)) END ELSE SET @text = RIGHT(@text (LEN(@text) - @index)) END RETURN END After that use below query SELECT * FROM ( select ROW_NUMBER() OVER(PARTITION BY ProductName order by ProductName) RNM* from tbl_Products P inner join (select value from fn_split(@SearchString' ')) as split_table on P.ProductName like '%'+split_table.value+'%' OR P.ProductCode like '%'+split_table.value+'%' ) AS T WHERE RNM=1

Bikesh Srivastava · Answer

You can use :- select @ from tbl_Products WHERE ProductName in ( String ) check it..

Bhuvan Pandey · Answer

Hi Devendra Try to enable Full-Text Search in SQL Server. Step to enable check with google. And use predicates (CONTAINS and FREETEXT).

Nitin Sontakke · Answer

I don't know you complete requirement however from whatever information is shared by you what you will have to is split the search string and add each word in there where clause. It's slightly complicated to implement but you will have to use dynamic SQL for that. Hope you already have a table returning function which will split the string if not google and you will get the code. SQL Server 2016 has built-in function STRING_SPLIT.