query to search related data in database

Devendra Kumar

10y883

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..

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

ketan borsdiya10y ago
Accepted 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

+1
Bikesh Srivastava10y ago
You can use :-
select@fromtbl_Products
WHEREProductNamein("String")
check it..
0
Bhuvan Pandey10y ago
Hi Devendra,
Try to enable Full-Text Search in SQL Server. Step to enable check with google.
And use predicates (CONTAINS and FREETEXT).
0
Nitin Sontakke10y ago
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.
0