sql - 按列精确单词或相同的前两个单词或相同的前三个单词分组

Question

我正在尝试按具有相同单词或相同前两个单词或相同前三个单词的列分组

表样:

name             
Brown sugar  
Brown sugar Creme
Tiger zoo
Tiger  
Blue red green
Blue red green yellow

预期结果:

name              cntnme
Brown sugar         2
Tiger zoo           1
Tiger               1
Blue red green      2

我只能按完全相同的词分组。

Select  name, count(name) as cntnme from tblsample group by name

Answer 1

听起来您想使用 SOUNDEX (Transact-SQL)运算符，例如:

create table dbo.Names (
  Name nvarchar(50) not null
);
insert dbo.Names values
  ('Brown sugar'),
  ('Brown sugar Creme'),
  ('Tiger zoo'),
  ('Tiger'),
  ('Blue red green'),
  ('Blue red green yellow'),
  ('Blue red green yellow orange');

select Name=min(Name), CntNme=count(1)
from dbo.Names
group by soundex(Name);

Name            CntNme
--------------- ------
Blue red green  3
Brown sugar     2
Tiger           2