-比較第一與第二個字元串,是否有連續的5個字元相同,如果有

--比較第一與第二個字元串,是否有連續的5個字元相同,如果有,傳回1,否則傳回0

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_compstr]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[f_compstr]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[序數表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [序數表]

GO

--為提高效率,最好建立一個固定的序數表

select top 1600 id=identity(int,1,1) into 序數表 from sysobjects a,sysobjects b

go

create function f_compstr(

@str1 varchar(8000),

@str2 varchar(8000)

) returns bit

as

begin

    declare @re bit

    if len(@str1)>len(@str2) set @re=case when exists(

        select 1

        from (select [email protected])a,序數表 b  --使用動态序數,改為: ,@tb b

        where b.id<=len(@str2)-4 and @str1 like '%'+substring(name,b.id,5)+'%'

        ) then 1 else 0 end

    else set @re=case when exists(

        select 1

        from (select [email protected])a,序數表 b  --使用動态序數,改為: ,@tb b

        where b.id<=len(@str1)-4 and @str2 like '%'+substring(name,b.id,5)+'%'

        ) then 1 else 0 end

    return(@re)

end

go

--調用示例

select dbo.f_compstr('浦東：田園小區148号405室','浦：田園小區148幢405室')

go

--使用例子,查詢不重複的記錄,重複的,隻顯示第一條:

declare @t table(id int identity(1,1),address varchar(1000))

insert into @t

select '浦東：田園小區148号405室'

union all select '浦：田園小區148幢405室'

union all select '浦：田園小區148幢405室'

union all select '浦：田園小區148幢405室'

union all select 'abcdefghijklmn'

union all select 'abcdefg'

union all select 'abcdefghijklmn'

union all select 'abcdefg'

union all select '123456768898'

union all select '123345'

select * from @t a

where id=(select min(id) from @t where dbo.f_compstr(a.address,address)=1)