SQL: Splitting a column into multiple words to search user input

For SQL Server, try this:

SELECT Word, COUNT(Word) * 10 AS WordCountFROM SourceTableINNER JOIN SearchWords ON CHARINDEX(SearchWords.Word, SourceTable.Name) > 0GROUP BY Word

What about this? (this is MySQL syntax, I think you only have to replace the CONCAT and do it with +)

SELECT names.id, count(searchwords.word) FROM names, searchwords WHERE names.name LIKE CONCAT('%', searchwords.word, '%') GROUP BY names.id

Then you would have a SQL result with the ID of the names-table and count of the words that match to that id.

You could do it via a common table expression that works out the weighting. For example:

--** Set up the example tables and dataDECLARE @Name TABLE (id INT IDENTITY, name VARCHAR(50));DECLARE @SearchWords TABLE (word VARCHAR(50));INSERT INTO @Name        (name)VALUES  ('Jack Nicholson')       ,('Henry Jack Blueberry')       ,('Pontiac Riddleson Jack')       ,('Fred Bloggs');INSERT INTO @SearchWords        (word)VALUES  ('Jack')       ,('Pontiac');--** Example SELECT with @Name selected and ordered by words in @SearchWordsWITH Order_CTE (weighting, id)AS (    SELECT COUNT(*) AS weighting         , id       FROM @Name AS n      JOIN @SearchWords AS sw        ON n.name LIKE '%' + sw.word + '%'      GROUP BY id)SELECT n.name     , cte.weighting  FROM @Name AS n  JOIN Order_CTE AS cte    ON n.id = cte.id ORDER BY cte.weighting DESC;

Using this technique, you can also apply a value to each search word if you wanted to. So you could make Jack more valueable than Pontiac. This would look something like this:

--** Set up the example tables and dataDECLARE @Name TABLE (id INT IDENTITY, name VARCHAR(50));DECLARE @SearchWords TABLE (word VARCHAR(50), value INT);INSERT INTO @Name        (name)VALUES  ('Jack Nicholson')       ,('Henry Jack Blueberry')       ,('Pontiac Riddleson Jack')       ,('Fred Bloggs');--** Set up search words with associated valueINSERT INTO @SearchWords        (word, value)VALUES  ('Jack',10)       ,('Pontiac',20)       ,('Bloggs',40);--** Example SELECT with @Name selected and ordered by words and values in @SearchWordsWITH Order_CTE (weighting, id)AS (    SELECT SUM(sw.value) AS weighting         , id       FROM @Name AS n      JOIN @SearchWords AS sw        ON n.name LIKE '%' + sw.word + '%'      GROUP BY id)SELECT n.name     , cte.weighting  FROM @Name AS n  JOIN Order_CTE AS cte    ON n.id = cte.id ORDER BY cte.weighting DESC;