CODEWITHSUNDEEP
sql-serverarrayst-sql
Alex
Alex

Reputation: 9720

SQL: Search the text that contains in arrays

I have one select query for search, my @SearchText can be composed of a few words

I want to separate the words into an array and select if some text contains my search text

Query:

ALTER PROCEDURE [dbo].[myStoreProcedure]
@SearchText varchar(100)
AS 
BEGIN
SELECT * FROM t0 
        WHERE 
ISNULL(@SearchText, '')='' OR t0.title LIKE '%' +@SearchText + '%')

Now I want: if I search string 'adam smith' or 'smith adam' result must be the same

How I can to convert my @SearchText into array and extract rows from table-t0 where title contains my @SearchText in any order

Upvotes: 1

Views: 7123

Answers (2)

Alex
Alex

Reputation: 9720

When I transmit parameter is need to delete double spaces and add between words "OR" like this: 

searchText = searchText.Replace("  ", " ").Replace(" ", " OR ");
command.Parameters.Add("@SearchText", SqlDbType.VarChar, 200).Value = searchText;

In SQL :

((@SearchText = '""') OR CONTAINS(t0.title, @SearchText))

Upvotes: 2

knkarthick24
knkarthick24

Reputation: 3216

Try using CONTAINS operator:

ALTER PROCEDURE [dbo].[myStoreProcedure]
@SearchText varchar(100)
AS 
BEGIN
SELECT * FROM t0 
        WHERE 
ISNULL(@SearchText, '')='' OR CONTAINS(t0.title,@SearchText)

More generic search you can do with CONTAINS operator.

Upvotes: 2

Related Questions