>Dear All,
>
>I have records in 'Area' table, like below:
>
>AreaCode
>80
>801
>81
>817
>8171
>81715
>818
>82
>821
>
>What SQL command should I use if I want to soft search into AreaCode with value '817145779' and I want it returns '8171' (because '8171' is the most accurate pattern in the table)
>
>Please help
>
>Thanks a lot in advance
>
>Regards
>Winanjaya

create table #test (AreaCode varchar(10))

insert into #test values ('80')
insert into #test values ('801')
insert into #test values ('81')
insert into #test values ('817')
insert into #test values ('8171')
insert into #test values ('81715')
insert into #test values ('818')
insert into #test values ('82')
insert into #test values ('821')

-- prefix
select top 1 AreaCode from #test where  '817145779' LIKE AreaCode+'%' ORDER BY LEN(AreaCode) DESC

-- internal
insert into #test values ('714577')

select top 1 AreaCode from #test where  '817145779' LIKE '%'+AreaCode+'%' ORDER BY LEN(AreaCode) DESC

-- suffix
select top 1 AreaCode from #test where  '817145779818' LIKE '%'+AreaCode ORDER BY LEN(AreaCode) DESC

DROP  table #test