Menu
I want to compare the individual words from the user input to individual words from a column in my table.
For example, consider these rows in my table:
ID Name
1 Jack Nicholson
2 Henry Jack Blueberry
3 Pontiac Riddleson Jack
Consider that the user's input is 'Pontiac Jack'. I want to assign weights/ranks for each match, so I can't use a blanket LIKE (WHERE Name LIKE @SearchString).
If Pontiac is present in any row, I want to award it 10 points. Each match for Jack gets another 10 points, etc. So row 3 would get 20 points, and rows 1 and 2 get 10.
I have split the user input into individual words, and stored them into a temporary table @SearchWords(Word).
But I can't figure out a way to have a SELECT statement that allows me to combine this. Maybe I'm going about this the wrong way?
Cheers, WT
574
You can do it using the following methods: Convert delimited string into XML, use XQuery to split the string, and save it into the table. Create a user-defined table-valued function to split the string and insert it into the table. Split the string using STRING_SPLIT function and insert the output into a table.
The STRING_SPLIT(string, separator) function in SQL Server splits the string in the first argument by the separator in the second argument. To split a sentence into words, specify the sentence as the first argument of the STRING_SPLIT() function and ' ' as the second argument. FROM STRING_SPLIT( 'An example sentence.
Yes, you can use SQL IN operator to search multiple absolute values: SELECT name FROM products WHERE name IN ( 'Value1', 'Value2', ... );
For SQL Server, try this:
SELECT Word, COUNT(Word) * 10 AS WordCount
FROM SourceTable
INNER JOIN SearchWords ON CHARINDEX(SearchWords.Word, SourceTable.Name) > 0
GROUP BY Word
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
