sql 2008 查询同字段有重复单词的项

[qq_17509519]

各位大神，有个sql查询的问题请教一下。
有表：name，adress，favorite三个字段
name adress favorite
1 BobBob CN swimiming;pingpang;swimming;
2 Jack US music
3 hankhank UN KALA;KALA;BENG

如上表中name，favorite字段的记录中有单词是重复出现的，如何用语句将第1、3条记录找出来？
希望大家能帮助一下

[qq_17509519]

引用 1 楼 fredrickhu 的回复:

只能拆分了再统计。

怎么拆分字符串呢？尤其是不带任何标点符号的 name字段

[霜寒月冷]

--drop table tb
create table tb(name varchar(20),adress varchar(20),favorite varchar(200))
insert into tb
select'BobBob','CN','swimiming;pingpangswimming;'   union all
 select'Jack','US','music'     union all                     
 select'hankhank','UN','KALA;KALA;BENG'      


go
select de,count(*) from
(
select tb.name ,adress,substring (favorite,a.number,charindex(';',favorite+';',a.number)-a.number) as de
from tb,master.dbo.spt_values a
where a.type='p' and a.number>=1 and a.number <len(favorite)  and substring (';'+favorite,a.number,1)=';'

) t group by de

--de	(无列名)
--BENG	1
--KALA	2
--music	1
--pingpangswimming	1
--swimiming	1

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2014-07-11 14:53:43
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 
--	Feb 10 2012 19:13:17 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([name] varchar(8),[adress] varchar(2),[favorite] varchar(28))
insert [tb]
select 'BobBob','CN','swimming;pingpang;swimming;' union all
select 'Jack','US','music' union all
select 'hankhank','UN','KALA;KALA;BENG'
--------------开始查询--------------------------
select * from tb where name in(
select name
from(
select name,favorite,count(1) as num
from
(
Select
    a.name,favorite=substring(a.favorite,b.number,charindex(';',a.favorite+';',b.number)-b.number) 
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.favorite)
where
     substring(';'+a.favorite,b.number,1)=';'
) as t
group by
   name,favorite
having 
   count(1)>1) as t)
----------------结果----------------------------
/* name     adress favorite
-------- ------ ----------------------------
BobBob   CN     swimming;pingpang;swimming;
hankhank UN     KALA;KALA;BENG

(2 行受影响)
*/

[shiyiwan]

很难，在没有单词表的情况下。因为一个字母也有可能是一个单词，比如A 那么如果name列中出现了ＡＮＡ，那么要不要算成一次重复？ 这个更多的是算法问题而不是sql问题。

[qq_17509519]

引用 4 楼 fredrickhu 的回复:

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2014-07-11 14:53:43
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 
--	Feb 10 2012 19:13:17 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([name] varchar(8),[adress] varchar(2),[favorite] varchar(28))
insert [tb]
select 'BobBob','CN','swimming;pingpang;swimming;' union all
select 'Jack','US','music' union all
select 'hankhank','UN','KALA;KALA;BENG'
--------------开始查询--------------------------
select * from tb where name in(
select name
from(
select name,favorite,count(1) as num
from
(
Select
    a.name,favorite=substring(a.favorite,b.number,charindex(';',a.favorite+';',b.number)-b.number) 
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.favorite)
where
     substring(';'+a.favorite,b.number,1)=';'
) as t
group by
   name,favorite
having 
   count(1)>1) as t)
----------------结果----------------------------
/* name     adress favorite
-------- ------ ----------------------------
BobBob   CN     swimming;pingpang;swimming;
hankhank UN     KALA;KALA;BENG

(2 行受影响)
*/

多谢多谢，我试验一下。 另外，请教一下，你命中1、3是因为favorite的拆分缘故，而对于name字段如何拆分呢，也就说如果第3条记录改为： name adress favorite 3 hankhank UN KALA 如何命中它？ 我感觉这个真的很难，关于无标点拆词~~~~~

[qq_17509519]

另外，补充一点，我这边name字段只会出现一个单词的连续重复，不会出现不同的单词的重复，如只可能是是：andandand，不会出现andorororandorandand这种情况。那么，在此条件下，如何进行sql的查询，将andandand命中？如何拆词如何比对，感觉好难！

[--小F--]

只能拆分了再统计。