正则匹配最大相同字符串求助
Clifford’s First Snow Day Clifford's First Snow Day
Clifford's First Snow Day Clifford's First Snow Day
Clifford's First Snow Day Clifford's First Snow Day Clifford's First Snow Day
Clifford's First Snow Day Clifford's First Snow Day
Clifford’s First Snow Day
Problem Problem
Problem
Good Good Study Good Good Study
null
如上数据库中题名字段中
当然每个
题名有0,1，甚至多个，而我只要一个题名，该怎样用正则匹配得到呢
也可以理解为 我如何匹配最大的一组相同的字符串呢
eg：我要得到 的题名分别为 《Clifford's First Snow Day》 《Problem》 《Good Good Study》 SQL?正则?Regex?C#
[解决办法]
DROPTABLE gg ,t_gg_r
DROP FUNCTION f_gg
CREATE TABLE gg(a VARCHAR(1000))
INSERT gg(a) SELECT 'Clifford''s First Snow Day Clifford''s First Snow Day'
union all select 'Clifford''s First Snow Day Clifford''s First Snow DAY'
union all select 'Clifford''s First Snow Day Clifford''s First Snow Day Clifford''s First Snow Day'
union all select 'Clifford''s First Snow Day Clifford''s First Snow DAY'
union all select 'Clifford''s First Snow Day'
union all select 'Problem Problem'
union all select 'Problem'
union all select 'Good Good Study Good Good Study'
union all select NULL
CREATE TABLE t_gg_R(x VARCHAR(100))
GO
CREATE FUNCTION f_gg(@a VARCHAR(1000))
RETURNS VARCHAR(100)
BEGIN
DECLARE @i INT
DECLARE @sT VARCHAR(1000)
DECLARE @R VARCHAR(100)
SET @i=1
IF @a IS NOT NULL
BEGIN
WHILE @i>0
BEGIN
SELECT @i=CHARINDEX(' ',@a,@i+1)
IF @i>0
BEGIN
SET @sT= LEFT(@a,@i-1)
IF rtrim(ltrim(REPLACE(@a,@sT,'')))='' SET @r= @sT
END
END
END
RETURN @r
END
GO
exec MASTER.dbo.xp_execresultset ' SELECT ''insert t_gg_r SELECT dbo.f_gg(''''''+replace(a,'''''''','''''''''''')+'''''')'' FROM gg','test'
SELECT DISTINCT * FROM t_gg_r
--result
/*x
----------------------------------------------------------------
NULL
Clifford's First Snow Day
Good Good Study
Problem
（所影响的行数为 4 行）
*/
with data(title) as
(
select 'Clifford''s First Snow Day Clifford''s First Snow Day' union all
select 'Clifford''s First Snow Day Clifford''s First Snow Day' union all
select 'Clifford''s First Snow Day Clifford''s First Snow Day Clifford''s First Snow Day' union all
select 'Clifford''s First Snow Day Clifford''s First Snow Day' union all
select 'Clifford''s First Snow Day' union all
select 'Problem Problem' union all
select 'Problem' union all
select 'Good Good Study Good Good Study' union all
select null
)
select title, sub=isnull(sub,title) from data t outer apply
(
select sub=left(title,len(title)/number) from master..spt_values where type='P' and number between 2 and len(title)/2 and title like '%'+left(title,len(title)/number)
) s
/*
title sub
----------------------------------------- -------------------------
Clifford's First Snow Day Clifford's First Snow Day Clifford's First Snow Day
Clifford's First Snow Day Clifford's First Snow Day Clifford's First Snow Day
Clifford's First Snow Day Clifford's First Snow Day Clifford's First Snow Day Clifford's First Snow Day
Clifford's First Snow Day Clifford's First Snow Day Clifford's First Snow Day
Clifford's First Snow Day Clifford's First Snow Day
Problem Problem Problem
Problem Problem
Good Good Study Good Good Study Good Good Study
NULL NULL
*/
