>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

Something like:

declare @i int
declare @length int
declare @key varchar(20)
declare @searchkey varchar(20)
declare @oldsearchkey varchar(20)
declare @lastmatch bit

set @i = 1
set @length = LEN(@key)
set @lastmatch = 0
set @key = '8171'

while @i <= @length AND @lastmatch = 0
begin
    set @searchkey = LEFT(@key,@i) + '%'
    if exists(select * from dbo.[Area] where @searchkey LIKE [AreaCode])
        set @oldsearchkey = @searchkey
    else
    begin
        @lastmatch = 1
    end
    set @i = @i + 1
end
if @lastmatch = 1
    select TOP 1 * from dbo.[Area] where @oldsearchkey LIKE [AreaCode]
        order by LEN([AreaCode])
else
begin
    select TOP 1 * from dbo.[Area] where @searchkey LIKE [AreaCode]
        order by LEN([AreaCode])
end