CODEWITHSUNDEEP
sql-serverdatabase
Praveen Singh
Praveen Singh

Reputation: 63

How to compare two identical values in SQL Server

@Var1=CHA000375972
@Var2=CHA000375972
@Var2=000375972

Here @Var1 and @Var2 are completely the same, but @Var3 is identical to @Var1 or @Var2. So how can we compare if two values look identical?

Here my goal is to get all values whether its complete same or almost same

select distinct a.SupplierInvoiceNumber
from #tmpSup1 a  
join #tmpSup2 b on a.SupplierInvoiceNumber = b.SupplierInvoiceNumber

Upvotes: 1

Views: 47

Answers (1)

SqlZim
SqlZim

Reputation: 38023

If you just want to compare the right most 9 characters, you can use this: 

right(@Var1,9) = right(@Var3,9)

select distinct a.SupplierInvoiceNumber
  from #tmpSup1 a 
    join #tmpSup2 b on right(a.SupplierInvoiceNumber,9)=right(b.SupplierInvoiceNumber,9)

If only one table has the SupplierInvoiceNumbers that are longer than 9 characters would need to be in the right() function the join condition could be simplified to:

    join #tmpSup2 b on right(a.SupplierInvoiceNumber,9)=b.SupplierInvoiceNumber

or 

    join #tmpSup2 b on a.SupplierInvoiceNumber=right(b.SupplierInvoiceNumber)

If one variable is always shorter than the other, but not always 9 characters, you can use this:

select distinct a.SupplierInvoiceNumber
  from #tmpSup1 a 
    join #tmpSup2 b on a.SupplierInvoiceNumber like '%'+b.SupplierInvoiceNumber

Upvotes: 1

Related Questions