use test;

go

/* create table */

if object_id('TBAddress') is not null

   drop table TBAddress;

go

create table TBAddress

(

  ID int ,

  Parent int not null ,

  LevelNo smallint not null ,

  Name nvarchar(50) not null ,

  constraint PK_TBAddress primary key ( ID )

);

go

create nonclustered index ix_TBAddress_Parent on TBAddress(Parent,LevelNo) include(Name) with(fillfactor=80,pad_index=on);

create nonclustered index ix_TBAddress_Name on TBAddress(Name)include(LevelNo)with(fillfactor=80,pad_index=on);

go

Use test

Go

if object_ID('[up_SearchAddressByNameV0]') is not null

    Drop Procedure [up_SearchAddressByNameV0]

Go

create proc up_SearchAddressByNameV0

(

    @Name nvarchar(200)

)

As

set nocount on

declare @sql nvarchar(max)

declare @tmp Table (Name nvarchar(50))

set @Name=@Name+' '

while patindex('%  %',@Name)>0

begin

    set @Name=replace(@Name,'  ',' ')

end

set @sql ='select ''' +replace(@Name,' ',''' union all select ''')+''''

insert into @tmp(Name) exec(@sql)

if object_id('tempdb..#tmp') is not null drop table #tmp

if object_id('tempdb..#') is not null drop table #

create table #tmp(ID int )

while @Name>''

begin

    insert into #tmp(ID)

    select a.ID  from TBAddress a where a.Name like '%'+substring(@Name,1,patindex('% %',@Name)-1)+'%' 

    set @Name=Stuff(@Name,1,patindex('% %',@Name),'')

end

;with cte_SearchParent as

(

    select a.ID,a.Parent,a.LevelNo,convert(nvarchar(500),a.Name) as AddressPath from TBAddress a where exists(select 1 from #tmp x where a.ID=x.ID)

    union all

    select a.ID,b.Parent,b.LevelNo,convert(nvarchar(500),b.Name+'/'+a.AddressPath) as AddressPath

        from cte_SearchParent a

        inner join TBAddress b on b.ID=a.Parent

            --and b.LevelNo=a.LevelNo -1

            and b.LevelNo>=1

)

select a.ID,a.AddressPath

    into #

    from cte_SearchParent  a

    where a.LevelNo=1 and exists(select 1 from @tmp x where a.AddressPath like '%'+x.Name+'%' having count(1)=(select count(1) from @tmp))

;with cte_result as

(

    select a.ID,a.LevelNo,b.AddressPath

        from TBAddress a

            inner join # b on b.ID=a.ID

    union all

    select b.ID,b.LevelNo,convert(nvarchar(500),a.AddressPath+'/'+b.Name) As AddressPath

        from cte_result a

            inner join TBAddress b on b.Parent=a.ID

                --and b.LevelNo=a.LevelNo+1

)

select distinct a.ID,a.AddressPath

    from cte_result a

    where not exists(select 1 from TBAddress x where x.Parent=a.ID)

    order by a.AddressPath

Go

Use test

Go

if object_ID('[up_SearchAddressByNameV1]') is not null

    Drop Procedure [up_SearchAddressByNameV1]

Go

create proc up_SearchAddressByNameV1

(

    @Name nvarchar(200)

)

As

set nocount on

declare @sql nvarchar(max),@contains nvarchar(500)

declare @tmp Table (Name nvarchar(50))

while patindex('%  %',@Name)>0

begin

    set @Name=replace(@Name,'  ',' ')

end

set @sql ='select ''' +replace(@Name,' ',''' union all select ''')+''''

set @contains='"'+replace(@Name,' ','*" Or "')+'*"'

insert into @tmp(Name) exec(@sql)

if object_id('tempdb..#') is not null drop table #

;with cte_SearchParent as

(

    select a.ID,a.Parent,a.LevelNo,convert(nvarchar(2000),a.Name) as AddressPath from TBAddress a where exists(select 1 from TBAddress x where contains(x.Name,@contains) And x.ID=a.ID)

    union all

    select a.ID,b.Parent,b.LevelNo,convert(nvarchar(2000),b.Name+'/'+a.AddressPath) as AddressPath

        from cte_SearchParent a

        inner join TBAddress b on b.ID=a.Parent

            --and b.LevelNo=a.LevelNo -1

            and b.LevelNo>=1

)

select a.ID,a.AddressPath

    into #

    from cte_SearchParent  a

    where a.LevelNo=1 and exists(select 1 from @tmp x where a.AddressPath like '%'+x.Name+'%' having count(1)=(select count(1) from @tmp))

;with cte_result as

(

    select a.ID,a.LevelNo,b.AddressPath

        from TBAddress a

            inner join # b on b.ID=a.ID

    union all

    select b.ID,b.LevelNo,convert(nvarchar(2000),a.AddressPath+'/'+b.Name) As AddressPath

        from cte_result a

            inner join TBAddress b on b.Parent=a.ID

                --and b.LevelNo=a.LevelNo+1

)

select distinct a.ID,a.AddressPath

    from cte_result a

    where not exists(select 1 from TBAddress x where x.Parent=a.ID)

    order by a.AddressPath

Go

Use test

Go

if object_ID('[up_SearchAddressByNameV2]') is not null

    Drop Procedure [up_SearchAddressByNameV2]

Go

create proc up_SearchAddressByNameV2

(

    @name nvarchar(200)

)

As

declare @contains nvarchar(500)

set nocount on

set @contains='"'+replace(@Name,' ','*" And "')+'*"'

select id,FullPath As AddressPath from TBAddress a where contains(a.FullPath,@contains) and not exists(select 1 from TBAddress x where x.Parent=a.ID) order by AddressPath

Go

Use test

Go

if object_ID('[up_SearchAddressByNameV3]') is not null

    Drop Procedure [up_SearchAddressByNameV3]

Go

create proc up_SearchAddressByNameV3

(

    @name nvarchar(200)

)

As

set nocount on

declare @sql nvarchar(max)

declare @tmp Table (Name nvarchar(50))

set @Name=rtrim(rtrim(@Name))

while patindex('%  %',@Name)>0

begin

    set @Name=replace(@Name,'  ',' ')

end

set @sql='select id,FullPath As AddressPath

    from TBAddress a where not exists(select 1 from TBAddress x where x.Parent=a.ID)

    '

set @sql +='And a.FullPath like ''%' +replace(@Name,' ','%'' And a.FullPath Like ''%')+'%'''

exec (@sql)

Go