时间戳

首页 > 解决方案 > 带有 SOUNDEX 的 PATINDEX

sql-server - 带有 SOUNDEX 的 PATINDEX

问题描述

想要使用 PATINDEX 和 SOUNDEX 搜索字符串。

我有下表包含一些示例数据，可以使用PATINDEX和搜索给定的字符串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');

要搜索的字符串：- '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

获取输出：

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

预期输出：

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

试过：

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

但得到意想不到的结果：

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

注意：我有100 Millions表中的记录要搜索。

标签： sql-servertsqlsql-server-2008-r2

解决方案

这是一个选项，考虑到您需要做的所有事情，不确定它会如何处理 1 亿条记录。你必须测试一下。

在高层次上，我如何理解这是你基本上需要的

  • 根据另一个字符串的单词搜索字符串中的所有单词
  • 返回原始字符串中该词等于或听起来像搜索词的字符起始位置。

您可以使用DIFFERENCE()进行比较：

DIFFERENCE 比较两个不同的 SOUNDEX 值，并返回一个整数值。该值衡量 SOUNDEX 值的匹配程度，范围为 0 到 4。值 0 表示 SOUNDEX 值之间的相似性较弱或没有相似性；4 表示 SOUNDEX 值非常相似，甚至完全匹配。

您需要根据空格 ' ' 拆分字符串，并且由于您是 2008 年，因此您必须滚动自己的函数。

我从这里使用了 XML 函数，https://sqlperformance.com/2012/07/t-sql-queries/split-strings，对于我的示例，如果您有自己的或想要使用，显然需要进行调整有些不同：

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

我切换并使用表变量来显示示例，我建议不要使用您拥有的数据量来创建和使用物理表。

选项 1 - 非动态：

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];
  • 使用我们将字符串拆分为单个单词的函数
  • 然后我们使用case语句来检查DIFFERENCE值
  • 如果 DIFFERENCE 值等于 4，我们将返回原始单词对字符串的 CHARINDEX 值。
  • 如果不等于我们返回 0

然后从那里得到一个基于原始字符串的最大值的问题：

          , 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]

为您提供最终结果：

在此处输入图像描述

选项 2 - 带有枢轴的动态：

我们将声明我们要搜索的字符串，将其拆分并在原始字符串中搜索那些单独的单词，然后旋转结果。

--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

推荐阅读