日期:2014-05-17 浏览次数:20538 次
DROP TABLE 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