Want to search the string using PATINDEX and SOUNDEX.

I have the following table with some sample data to search the given string using PATINDEX and SOUNDEX.

create table tbl_pat_soundex
(
    col_str varchar(max)
);

insert into tbl_pat_soundex values('Smith A Steve');
insert into tbl_pat_soundex values('Steve A Smyth');
insert into tbl_pat_soundex values('A Smeeth Stive');
insert into tbl_pat_soundex values('Steve Smith A');
insert into tbl_pat_soundex values('Smit Steve A');

String to search:- 'Smith A Steve'

SELECT col_str,PATINDEX('%Smith%',col_str) [Smith],PATINDEX('%A%',col_str) [A],PATINDEX('%Steve%',col_str) [Steve]
FROM tbl_pat_soundex

Getting Output:

col_str         Smith   A   Steve
---------------------------------
Smith A Steve   1       7   9
Steve A Smyth   0       7   1
A Smeeth Stive  0       1   0
Steve Smith A   7       13  1
Smit Steve A    0       12  6

Expected Output:

col_str         Smith   A   Steve
---------------------------------
Smith A Steve   1       7   9
Steve A Smyth   9       7   1
A Smeeth Stive  3       1   10
Steve Smith A   7       13  1
Smit Steve A    1       12  6

Tried:

SELECT col_str,
        PATINDEX('%'+soundex('Smith')+'%',soundex(col_str)) [Smith],
        PATINDEX('%'+soundex('A')+'%',soundex(col_str)) [A],
        PATINDEX('%'+soundex('Steve')+'%',soundex(col_str)) [Steve]
FROM tbl_pat_soundex

But getting unexpected result:

col_str         Smith   A   Steve
---------------------------------
Smith A Steve   1       0   0
Steve A Smyth   0       0   1
A Smeeth Stive  0       1   0
Steve Smith A   0       0   1
Smit Steve A    1       0   0

Note: I have 100 Millions of records in the table to search for.

Here's one option, not sure how it would perform with 100 million records considering all that you need to do. You'll have to test that out.

At a high level how I understand this is you basically need

Search all words in a string based on the words of another string
Returning the character starting position in the original string where that word equals or sounds like the search word.

You can use DIFFERENCE() for the comparison:

DIFFERENCE compares two different SOUNDEX values, and returns an integer value. This value measures the degree that the SOUNDEX values match, on a scale of 0 to 4. A value of 0 indicates weak or no similarity between the SOUNDEX values; 4 indicates strongly similar, or even identically matching, SOUNDEX values.

You'll need to split the string based on the space ' ' and since you're 2008 you'd have to roll your own function.

I used the XML function from here, https://sqlperformance.com/2012/07/t-sql-queries/split-strings, for my examples, you'll obviously need to adjust if you have your own or want to use something different:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

I switched and use table variables to show the example, I would suggest not doing that with the amount of data you have and create and use physical tables.

Option 1 - Not dynamic:

DECLARE @tbl_pat_soundex TABLE
    (
        [col_str] VARCHAR(MAX)
    );

INSERT INTO @tbl_pat_soundex
VALUES ( 'Smith A Steve' )
,( 'Steve A Smyth' )
,( 'A Smeeth Stive' )
,( 'Steve Smith A' )
,( 'Smit Steve A' )

SELECT DISTINCT [aa].[col_str]
              , MAX([aa].[Smith]) OVER ( PARTITION BY [aa].[col_str] ) AS [Smith]
              , MAX([aa].[A]) OVER ( PARTITION BY [aa].[col_str] ) AS [A]
              , MAX([aa].[Steve]) OVER ( PARTITION BY [aa].[col_str] ) AS [Steve]
FROM   (
           SELECT      [a].[col_str]
                     , CASE WHEN DIFFERENCE([b].[item], 'Smith') = 4 THEN
                                CHARINDEX([b].[item], [a].[col_str])
                            ELSE 0
                       END AS [Smith]
                     , CASE WHEN DIFFERENCE([b].[item], 'A') = 4 THEN
                                CHARINDEX([b].[item], [a].[col_str])
                            ELSE 0
                       END AS [A]
                     , CASE WHEN DIFFERENCE([b].[item], 'Steve') = 4 THEN
                                CHARINDEX([b].[item], [a].[col_str])
                            ELSE 0
                       END AS [Steve]
           FROM        @tbl_pat_soundex [a]
           CROSS APPLY [dbo].[SplitStrings_XML]([a].[col_str], ' ') [b]
       ) AS [aa];

Using the function we split the string into individual words
Then we use a case statement to check the DIFFERENCE value
If that DIFFERENCE value equals 4 we then return the CHARINDEX value of the original word against string.
If doesn't equal we return 0

Then from there it's a matter of getting the max value of each based on the original string:

          , MAX([aa].[Smith]) OVER ( PARTITION BY [aa].[col_str] ) AS [Smith]
          , MAX([aa].[A]) OVER ( PARTITION BY [aa].[col_str] ) AS [A]
          , MAX([aa].[Steve]) OVER ( PARTITION BY [aa].[col_str] ) AS [Steve]

To get you your final results:

Option 2 - Dynamic with a pivot:

We'll declare the string we want to search, split that out and search for those individuals words in the original string and then pivot the results.

--This example is using global temp tables as it's showing how
--to build a dynamic pivot
IF OBJECT_ID('tempdb..##tbl_pat_soundex') IS NOT NULL
  DROP TABLE [##tbl_pat_soundex];

IF OBJECT_ID('tempdb..##tbl_col_str_SearchString') IS NOT NULL
  DROP TABLE [##tbl_col_str_SearchString];

CREATE TABLE [##tbl_pat_soundex]
    (
        [col_str] VARCHAR(MAX)
    );

INSERT INTO [##tbl_pat_soundex]
VALUES ( 'Smith A Steve' )
     , ( 'Steve A Smyth' )
     , ( 'A Smeeth Stive' )
     , ( 'Steve Smith A' )
     , ( 'Smit Steve A' );

--What are you searching for?
DECLARE @SearchString NVARCHAR(200);
SET @SearchString = N'Smith A Steve';

--We build a table we load with every combination of the words from the string and the words from the SearchString for easier comparison.
CREATE TABLE [##tbl_col_str_SearchString]
    (
        [col_str] NVARCHAR(MAX)
      , [col_str_value] NVARCHAR(MAX)
      , [SearchValue] NVARCHAR(200)
    );

--Load that table for comparison
--split our original string into individual words
--also split our search string into individual words and give me all combinations.
INSERT INTO [##tbl_col_str_SearchString] (
                                             [col_str]
                                           , [col_str_value]
                                           , [SearchValue]
                                         )
            SELECT      DISTINCT [a].[col_str]
                               , [b].[item]
                               , [c].[item]
            FROM        [##tbl_pat_soundex] [a]
            CROSS APPLY [dbo].[SplitStrings_XML]([a].[col_str], ' ') [b]
            CROSS APPLY [dbo].[SplitStrings_XML](@SearchString, ' ') [c]
            ORDER BY    [a].[col_str];

--Then we can easily compare each word and search word for those that match or sound alike using DIFFERNCE()
SELECT [col_str], [col_str_value], [SearchValue], CASE WHEN DIFFERENCE([col_str_value], [SearchValue]) = 4 THEN CHARINDEX([col_str_value], [col_str]) ELSE 0 END AS [Match] FROM ##tbl_col_str_SearchString

--Then we can pivot on it
--and we will need to make it dynamic since we are not sure what what @SearchString could be.
DECLARE @PivotSQL NVARCHAR(MAX);
DECLARE @pivotColumn NVARCHAR(MAX);

SET @pivotColumn = N'[' + REPLACE(@SearchString, ' ', '],[') + N']';

SET @PivotSQL = N'SELECT * FROM (
SELECT [col_str], [SearchValue], CASE WHEN DIFFERENCE([col_str_value], [SearchValue]) = 4 THEN CHARINDEX([col_str_value], [col_str]) ELSE 0 END AS [Match] FROM ##tbl_col_str_SearchString
) aa
PIVOT (MAX([Match]) FOR [SearchValue] IN (' + @pivotColumn
                + N')) AS MaxMatch
ORDER BY [MaxMatch].[col_str]
';

--Giving us the final results.
EXEC sp_executesql @PivotSQL