CODEWITHSUNDEEP
sql
Alessio Raddi
Alessio Raddi

Reputation: 622

SQL - Select rows by entering two words

I have a table that contains a field named Item_Name, which can be composed by one or more words.

E.g. Item_Name -> Yellow Towel

I'd like to have a query that selects that value by entering "yel tow". So far I've a query that selects that value by entering a single word

SELECT Item_Name FROM Articles WHERE Item_Name LIKE '%yel'

And it works fine.

Thanks in advance for your suggestions.

Upvotes: 0

Views: 2276

Answers (3)

Rey
Rey

Reputation: 196

Hope this helps

Create test data:

DECLARE @words NVARCHAR(40) = 'yel tow'  

CREATE Table #table (vals varchar(50))

INSERT INTO #table (vals) SELECT 'yellow towels'

This is where the work happens

SELECT *
FROM #table
WHERE vals like replace(@words,' ','% ')+'%'

Cleanup 

DROP TABLE #table

Result Will be

yellow towels

So this should handle any number of words you throw at it. Note: The replace leaves the space so it will expect a space between each word.

Upvotes: 0

Debabrata
Debabrata

Reputation: 122

You can use the following script

SELECT * FROM Articles WHERE Item_Name LIKE 'yel%' or Item_Name LIKE '% yel%'

Upvotes: 0

Pawel Czapski
Pawel Czapski

Reputation: 1864

Below should be what are you looking for, as your attempt is like '%yel', I have used % at beginning as well

declare @search nvarchar(10) = 'yel tow'

declare @my_table table (test nvarchar(30))
insert into @my_table
values ('yellow towel'),
    ('y t'),
    ('yel tow'),
    ('random text')

select * from @my_table where test like '%' + replace(@search,' ', '%') + '%'

Output:

yellow towel
yel tow

Or straight forward select, as pointed in comments:

SELECT Item_Name FROM Articles WHERE Item_Name LIKE '%yel%tow%'

Upvotes: 1

Related Questions