CODEWITHSUNDEEP
sql-servert-sqljoin
Geo
Geo

Reputation: 336

Case Sensitive join TSQL

I am at a bit of a standstill here. I have a simple left outer join to a table that is returning an ID.

My code is as 

Select distinct TenantID
,Name
,Name2
,TenantNumber
,Cashname 
From Tenants 
LEFT OUTER JOIN tCash 
on TenantNumber = CashNumber 
and tMoney.CashName = Tenants.Name2

My result set is as follows:

**TenantID | Name      | Name2          | TenantNo | CashName**

100      |MyShop     | John's shop    | 12345    |John's shop

999      |MyShop     | John's Shop    | 12345    |John's shop

My Issue: for all intents and purposes, "John's shop" IS different from "John's Shop" - I am correctly joining my money table on the TenantNo and then on Name2, but name 2 is different by Case.

Question: Is there any way to differentiate a join based on case sensitivity? I would not want to use UPPER or LOWER due to the fact that it would ruin the case on reporting.

Thanks!

Adding Table information below, please assume all columns are trimmed of whitespace.

tMoney

CashNumnbr |  CashName
102504       Bill's Place     

102374       Tom's Shop      

12345        John's Shop

12345        John's shop

Tenants

   TenantID | Name     | Name2            |TenantNumber 

  1         |MyShop    | John's Shop     | 12345

  2         |MyShop    | John's shop     | 12345

  3         |Shoppee   |  Bill's Place   | 102504        

  4         | Shop2    | Toms Shop      | 102374

Since I want to join to get the correct TenantID for an AR report, I would want to make sure I am always bringing in the correct tenant. If the case is different, is there anything I can write to differentiate a situation like John's Shop?

Upvotes: 5

Views: 21213

Answers (2)

thecoolmacdude
thecoolmacdude

Reputation: 2277

If COLLATE ends up being too slow due to a lack of indexing, you could also do something like the below, where each 30 below must match the length of each column to avoid an invalid comparison.

LEFT OUTER JOIN tCash ON 
    TenantNumber = CashNumber
    AND CONVERT(VARBINARY(30),LTRIM(RTRIM(tMoney.CashName))) = CONVERT(VARBINARY(30),LTRIM(RTRIM(Tenants.Name2)))

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453328

The problem is that in the second row of your results "John's Shop" shouldn't have matched "John's shop"?

You can use a case sensitive collation.

This is probably best achieved by altering the collation of the columns involved to allow index use but you can also do it at run time with an explicit COLLATE clause as below.

SELECT DISTINCT TenantID,
                Name,
                Name2,
                TenantNumber,
                Cashname
FROM   Tenants
       LEFT OUTER JOIN tCash
         ON TenantNumber = CashNumber
            AND tMoney.CashName = Tenants.Name2 COLLATE Latin1_General_100_CS_AS

The comments about joining on id instead of name are likely correct though and would negate the need to do this at all.

Upvotes: 24

Related Questions