How to retrieve the matched word Text Search

user_10104547 0
Viewed 60

How to retrieve the word text search rows. For example, if I search input as 'water Bottel', I need an output like 'Water Soap Bottel','Water Milk Bottel','Water Copper Bottel'. I know how to use LIKE Operator.

IF OBJECT_ID('tempdb..#SearchText') IS NOT NULL
    DROP TABLE #SearchText



CREATE TABLE #SearchText
(
    ProductId INT,
    ProductName VARCHAR(500)
)

INSERT INTO #SearchText VALUES

    (1,'Water Soap Bottel'),
    (2,'Water Milk Bottel'),
    (3,'Wooden Box'),
    (4,'Water Plastic Bottel'),
    (5,'Water Copper Bottel')
sql-server sql
3 Answers

You can do this:

DECLARE @SearchTerm VARCHAR(100) = 'water bottel'

SELECT *
FROM #SearchText
WHERE ProductName LIKE '%' + REPLACE(@SearchTerm, ' ', '%') + '%'

However if you need to also return a row containing the phrase bottel water, the order of those words will mean the above doesn't work. In that case you will need to break up the query into multiple words, for example:

SELECT *
FROM #SearchText
WHERE ProductName LIKE '%water%'
AND ProductName LIKE '%bottel%'

If you have a full text index on that table, you could use CONTAINS:

SELECT *
FROM #SearchText
WHERE CONTAINS(ProductName, '"water" AND "bottel"')
user_16630010

You can split data on space and search if the splitted word match the splitted productname :

If all searched word must matches :

SELECT *
FROM   #SearchText st
WHERE  ( SELECT COUNT(value)
         FROM   STRING_SPLIT('water Bottel', ' ') ) = ( SELECT COUNT(*)
                                                        FROM   STRING_SPLIT(st.ProductName, ' ') AS sv
                                                        JOIN   STRING_SPLIT('water Bottel', ' ') AS ss
                                                          ON sv.value = ss.value )

If one or more word must match:

SELECT *
FROM   #SearchText st
WHERE  EXISTS ( SELECT 1
                FROM   STRING_SPLIT(st.ProductName, ' ') AS sv
                JOIN   STRING_SPLIT('water Bottel', ' ') AS ss
                  ON sv.value = ss.value )

Note : The string_split function is built-in on SQL Server 2016, you can search a create a custom implementation if you use a lower version.

user_27517430

Use LIKE operator.

SELECT
    *
FROM #SearchText
WHERE LOWER(ProductName) LIKE '%water%'
        AND LOWER(ProductName) LIKE '%bottel%'

You can ommit LOWER(...) function if collaction of column/database/server is case insensitive.

user_203820
Related