Optimal search string in the where clause

Question

Want to search the string using PATINDEX and SOUNDEX within the WHERE clause or any optimal way.

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');

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

String to search:- 'Smith A Steve'

SELECT col_str
FROM tbl_pat_soundex
WHERE PATINDEX('%Smith%',col_str) >= 1 AND PATINDEX('%A%',col_str) >= 1 AND PATINDEX('%Steve%',col_str) >= 1

Getting Output:

col_str
--------------
Smith A Steve
Steve Smith A

Expected Output:

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

Tried:

1:

SELECT col_str
FROM tbl_pat_soundex
WHERE PATINDEX('%Smith%',col_str) >= 1 AND 
      PATINDEX('%A%',col_str) >= 1 AND 
      PATINDEX('%Steve%',col_str) >= 1

2:

SELECT col_str
FROM tbl_pat_soundex
WHERE PATINDEX('%'+SOUNDEX('Smith')+'%',SOUNDEX(col_str)) >= 1 AND 
      PATINDEX('%'+SOUNDEX('A')+'%',SOUNDEX(col_str)) >= 1 AND 
      PATINDEX('%'+SOUNDEX('Steve')+'%',SOUNDEX(col_str)) >= 1

3:

SELECT col_str
FROM tbl_pat_soundex    
WHERE DIFFERENCE('Smith',col_str) = 4 AND 
      DIFFERENCE('A',col_str) =4 AND 
      DIFFERENCE('Steve',col_str) = 4

4:

--Following was taking huge time(was kept running more than 20 minutes) to execute.
SELECT DISTINCT col_str
FROM tbl_pat_soundex [a]
CROSS APPLY SplitString([a].[col_str], ' ') [b]
WHERE DIFFERENCE([b].Item,'Smith') >= 1 AND 
      DIFFERENCE([b].Item,'A') >= 1 AND 
      DIFFERENCE([b].Item,'Steve') >= 1

Isn't this basically a duplicate of your previous [question](https://stackoverflow.com/questions/53519032/patindex-with-soundex)? — Thom A, Nov 29 '18 at 10:28
@Larnu, Slight different! The query number 4 works fine but not for the huge records, so started the conversation again with some more details. — MAK, Nov 29 '18 at 10:36
When you say it doesn't work fine for "huge" records, what do you mean? A value of `Item` or `[a].[col_str]`that has a lot of characters, or when `tbl_pat_soundex` has a lot (the 100M you are talking about?) of rows? That query is very very unlikely to be SARGable, so it is most definitely going to be slow for a large amount of rows; there unlikely to be much you can do about that. You're going to be forced to complete a table scan on the table, which has 100M rows, and then the SQL server will need to perform expensive functions against **every** value. It's a very expensive (and slow) query, — Thom A, Nov 29 '18 at 10:40
The huge records related to `[a].[col_str]`. Any better approach? — MAK, Nov 29 '18 at 10:44
That doesn't answer the question @MAK. Using a word to define the same word doesn't define it. "Huge is huge"? Great. what is "huge"? — Thom A, Nov 29 '18 at 10:56
@Larnu, apologies! My bad. I mean to say I have over 100 millions records in the table. — MAK, Nov 29 '18 at 10:59
Then, I would suggest, there's very little you can do here for the reasons I gave above. You will probably be better off doing the processing in batches, so as not to overload the server, but it's always going to perform poorly. — Thom A, Nov 29 '18 at 11:04
I would recommend researching [FREETEXTTABLE](https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/freetexttable-transact-sql?view=sql-server-2017) and [CONTAINSTABLE](https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/containstable-transact-sql?view=sql-server-2017). Your database column will need to be full-text indexed, but seems like your best option. — Cardi DeMonaco Jr, Nov 29 '18 at 13:33

Shnugo · Accepted Answer · 2018-11-29T16:07:32.863

With such a lot of rows the only hint I can give you is: Change the design. Each name part should live in a separate column...

The following will work, but I promise it will be slow...

--set up a test db

USE master;
GO
CREATE DATABASE shnugo;
GO
USE shnugo;
GO

--your table, I added an ID-column

create table tbl_pat_soundex
(
    ID INT IDENTITY --needed to distinguish rows
   ,col_str varchar(max)
);
GO

--A function, which will return a blank-separated string as a alphabetically sorted list of distinct soundex values separated by /: "Smith A Steve" comes back as /A000/S310/S530/

CREATE FUNCTION dbo.ComputeSoundex(@str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @tmpXML XML=CAST('<x>' + REPLACE((SELECT @str AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML);
    RETURN (SELECT DISTINCT '/' + SOUNDEX(x.value('text()[1]','varchar(max)')) AS [se]
            FROM @tmpXML.nodes('/x[text()]') A(x)
            ORDER BY se
            FOR XML PATH(''),TYPE).value('.','nvarchar(max)') + '/';
END
GO

--Add a column to store a computed soundex-chain permanently

ALTER TABLE tbl_pat_soundex ADD SortedSoundExPattern VARCHAR(MAX);
GO

--We need a trigger to maintain the computed soundex-chain on any insert or update

CREATE TRIGGER RefreshComputeSoundex ON tbl_pat_soundex
FOR INSERT,UPDATE
AS
BEGIN
    UPDATE s SET SortedSoundExPattern=dbo.ComputeSoundex(i.col_str)
    FROM tbl_pat_soundex s
    INNER JOIN inserted i ON s.ID=i.ID;
END
GO

--test data

insert into tbl_pat_soundex(col_str) values
 ('Smith A Steve')
,('Steve A Smyth')
,('A Smeeth Stive')
,('Steve Smith A')
,('Smit Steve A')
,('Smit Steve') --no A
,('Smit A') --no Steve
,('Smit Smith Robert Peter A') --add noise
,('Shnugo'); --something else entirely

--check the intermediate result

SELECT * 
FROM tbl_pat_soundex

/*
+----+---------------------------+-----------------------+
| ID | col_str                   | SortedSoundExPattern  |
+----+---------------------------+-----------------------+
| 1  | Smith A Steve             | /A000/S310/S530/      |
+----+---------------------------+-----------------------+
| 2  | Steve A Smyth             | /A000/S310/S530/      |
+----+---------------------------+-----------------------+
| 3  | A Smeeth Stive            | /A000/S310/S530/      |
+----+---------------------------+-----------------------+
| 4  | Steve Smith A             | /A000/S310/S530/      |
+----+---------------------------+-----------------------+
| 5  | Smit Steve A              | /A000/S310/S530/      |
+----+---------------------------+-----------------------+
| 6  | Smit Steve                | /S310/S530/           |
+----+---------------------------+-----------------------+
| 7  | Smit A                    | /A000/S530/           |
+----+---------------------------+-----------------------+
| 8  | Smit Smith Robert Peter A | /A000/P360/R163/S530/ |
+----+---------------------------+-----------------------+
| 9  | Shnugo                    | /S520/                |
+----+---------------------------+-----------------------+
*/

--Now we can start to search:

DECLARE @StringToSearch VARCHAR(MAX)=' A Steve';

WITH SplittedSearchString AS
(
    SELECT soundexCode.value('text()[1]','nvarchar(max)') AS SoundExCode
    FROM (SELECT CAST('<x>' + REPLACE(dbo.ComputeSoundex(@StringToSearch),'/','</x><x>') + '</x>' AS XML)) A(x)
    CROSS APPLY x.nodes('/x[text()]') B(soundexCode)
)
SELECT a.ID,col_str
FROM tbl_pat_soundex a
INNER JOIN SplittedSearchString s On SortedSoundExPattern LIKE '%/' +  s.SoundExCode + '/%'
GROUP BY ID,col_str
HAVING COUNT(ID)=(SELECT COUNT(*) FROM SplittedSearchString)
ORDER BY ID 
GO

--clean-up

USE master;
GO
DROP DATABASE shnugo;

Short explanation

This is how it works:

The cte will use the same function to return a soundex-chain of alle the input's fragments
The query will then INNER JOIN this with a LIKE test --this will be sloooooow...
The final check is, if the number of hits is the same as number of fragments.

And a final hint: If you want to search for an exact match, but you want to include different writings you can just directly compare the two strings. You might even place an index on the new column SortedSoundExPattern. Due to the way of creation all kinds of "Steven A Smith", "Steeven a Smit" and even in differing order like "Smith Steven A" will produce exactly the same pattern.

score 0 · Answer 2 · answered Nov 29 '18 at 13:24

In my view, you should try to use dynamic SQL.

For example, you have a table:

create table tbl_pat_soundex
(
    id int,
    col_str varchar(max)
)

And you have an the following clustered index or any other index(table with over 100 million rows should have some index):

CREATE NONCLUSTERED INDEX myIndex ON dbo.tbl_pat_soundex(id) INCLUDE (col_str)*/

So try to create the following dynamic SQL query based on your logic and execute it. The wish result should look like this:

DECLARE @statement NVARCHAR(4000)
SET @statement = N'
SELECT col_str
FROM tbl_pat_soundex
WHERE col_str like '%Smith%' AND id > 0
UNION ALL
SELECT col_str
FROM tbl_pat_soundex
WHERE col_str like '%Steve%' AND id > 0
UNION ALL
SELECT col_str
FROM tbl_pat_soundex
WHERE 
    PATINDEX('%Smith%',col_str) >= 1 AND PATINDEX('%A%',col_str) >= 1 AND 
    PATINDEX('%Steve%',col_str) >= 1
    AND id > 0'

Basically, what we do is creating single search queries which will have index seek and then combine all results.

This query will have index seek as we use predicate id > 0(assuming that all ids are greater than 0 or you can write your own negative number):

SELECT col_str
FROM tbl_pat_soundex
WHERE col_str like '%Smith%' AND id > 0

Optimal search string in the where clause

Tried:

2 Answers2

Short explanation