How to Replace a part of string in Left Join Statement in SQL

Question

I have sql statement

 LEFT JOIN SeniorCitizen on FinishedTransaction.SCID = SeniorCitizen.OSCAID

SCID has 1234 OSCAID has 1234/102938

How can I remove /102938 so that it matches

Answer 1

Hmmm, one method is to use LIKE:

ON SeniorCitizen.OSCAID LIKE FinishedTransaction.SCID + '/%'

No guarantees on performance, but this should do the join correctly.

EDIT:

You can do this operation efficiently by using a computed column and then an index on the computed column.

So:

alter table SeniorCitizen
    add OSCAIDshort as ( cast(left(OSCAID, CHARINDEX('/', OSCAID) - 1) as int) );

create index idx_SeniorCitizen_OSCAIDshort on SeniorCitizen(OSCAIDshort);

(The cast presumes that the SCID column is an integer.)

Then you can use this in the join as:

LEFT JOIN SeniorCitizen on FinishedTransaction.SCID = SeniorCitizen.OSCAIDshort

This formulation can use the index on the computed column and hence is probably the fastest way to do the join.

Answer 2

If you knew that the length of the numbers you were comparing was always 4, you could use SUBSTRING, like so:

LEFT JOIN SeniorCitizen on FinishedTransaction.SCID = SUBSTRING(SeniorCitizen.OSCAID, 1, 4)

to just grab the first four characters from OSCAID for the comparison.

However, even if you knew the length was always 4, it's still safer to assume that you won't know the length, because maybe at some point in the future the length grows. And if it does, your query can scale with it with no issues. To do this, you can use a combination of SUBSTRING and CHARINDEX, like so:

LEFT JOIN SeniorCitizen on FinishedTransaction.SCID = SUBSTRING(SeniorCitizen.OSCAID, 1, CHARINDEX('/', SeniorCitizen.OSCAID, 0))

This will start at the first character in OSCAID and continue reading until it finds a /. So if the string is 1234/102938, it'll return 1234. And if grows to 123456/102938, it'll return 123456.

Be sure to check out the docs for each of those functions to get a better understanding of their capabilities:

SUBSTRING: https://msdn.microsoft.com/en-us/library/ms187748.aspx

CHARINDEX: https://msdn.microsoft.com/en-us/library/ms186323.aspx