CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
StackUser
StackUser

Reputation: 5398

Find the closest matching row in sql server

I have the following table.

TableName: Mask

Columns:

MaskId INT
MaskCode VARCHAR(100)

If I pass Input as MaskId=1 then I am getting two records with different maskcodes.

select maskcode from mask where maskid=1

G******
G12****

I want to find the closest matching row among these two rows. I am going to introduce one more input @Maskcode

Scenario-1:

Now I am going to pass two inputs

MaskId=1, maskcode=G123456

My query would be something like this

select maskcode from mask where maskid=1 and maskcode='G123456'

In this case I need G12**** row because this is the closest match among the two rows.

Scenario-2:

MaskId=1, maskcode=G999999

In this case I need G***** row.

Scenario-3:

For MaskId=2 I have two records below

6h****
6h****

My input for this case would be Maskid=2 and Maskcode=6h1234 I need two rows in this case since we cannot find the most closest in these two.

I tried the below but it returns two rows.

select *From mask
where @Maskcode
like '%'+replace(MaskCode,'*',''+'%'

Please provide any ideas.

Upvotes: 0

Views: 190

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

Declare @MaskCode varchar(25) = '6h1234'

Select * 
 From  (
        Select *,Rnk = Dense_Rank() over (Order By Len(Replace(MaskCode,'*','')) Desc)
         From  MaskCode
         Where @MaskCode Like Replace(MaskCode,'*','')+'%'
       ) A
 Where Rnk=1

With @MaskCode = '6h1234' Returns

MaskID  MaskCode    Rnk
2       6h****      1
2       6h****      1

With @MaskCode = 'G123456' Returns

MaskID  MaskCode    Rnk
1       G12****     1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269923

I think you want to use TOP and LEN and LIKE:

select top 1 m.*
from mask
where @Maskcode like '%' + replace(MaskCode, '*', '') + '%'
order by len(replace(MaskCode, '*', '')) desc;

By the way, do you really want the wildcard at the beginning? Or perhaps you want:

where @Maskcode like replace(MaskCode, '*', '%')

Multiple occurrences of % next to each other will not affect the LIKE pattern semantics.

Upvotes: 1

Related Questions