SQL: declaring multiple variables dependant on other values - store procedures

Question

I'm creating a store procedure for fun.

The basic idea is a that a string is inputed and names from a table are outputted. This is how I want it to work:

1. Input string
2. Count the number of words
3. Break the string up into separate words (variables)
4. Search where each word is like a name in a table.
5. Output the similar names

My problem is that the number of words in each string may differ between input strings. I could quite easily build a set of WHILE/IF statements for 1 word, 2 words, 3 words etc.

However I want it to only declare a variable for each word. For instance, say I input the string: 'the rose'. The store procedure would then declare only 2 variables (@word1, @word2).

Here is the beginning of some code I am writing:

CREATE PROCEDURE postcodes.sp_postcode @any_string VARCHAR(1000)
    AS
    BEGIN

    -------count number of words-------

    DECLARE @num_of_words INT
    SELECT @num_of_words = LEN(@any_string) - LEN(REPLACE(@any_string,' ',''))

    -------searching similar words-----

    WHILE @num_of_words = 1
        BEGIN
            SELECT name
            FROM [kats].[postcodes].[open_pubs]
            WHERE name LIKE '%' + @any_string + '%'
        END

    WHILE @num_of_words = 2
        BEGIN
            DECLARE @word1
            DECLARE @word2
            SET @word1 = SUBSTRING(@any_string,0,(CHARINDEX(' ',@any_string,0)))
            SET @word2 = SUBSTRING(@any_string,(CHARINDEX(' ',@name,0)+1),LEN(@any_string)) 

            SELECT name
            FROM [kats].[postcodes].[open_pubs]
            WHERE name LIKE '%' + @word1 + '%'
            OR name LIKE '%' + @word2 + '%'
        END

As you can see I have build statements for each case in word length. This can go on indefinitely. I want something that like:

Pseudo code/ideas:

DECLARE @word(word_number)  [* number_of_words]
SET @word(word_number) = SUBSTRING(@any_string)

SELECT name FROM X
WHERE name LIKE '%' + @word1 + '%'
OR name LIKE '%' + @word2 + '%'
etc

I essentially don't want to continue to make WHILE statements for any string.

Kind regards.

Answer 1

For SQL Server 2016 or above the below version works perfectly as it uses the STRING_SPLIT built-in function. For older ones, you just need to use one of the alternate mechanisms to convert a delimited string into a table.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

create table Test (name varchar(max))

insert Test values 
  ('abc'), ('def'), ('efg'), ('hij')

Query 1:

DECLARE @tags nvarchar(max) = 'ab ef fg'  

SELECT * FROM Test t
WHERE EXISTS (SELECT * FROM STRING_SPLIT(@tags, ' ') s 
              WHERE t.name LIKE '%' + s.value + '%')

Results:

| name |
|------|
|  abc |
|  def |
|  efg |