>>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

Winan,

Full-text indexing will also give you the results that you are looking for with a one-line query.