If you have a requirement to bulk query source 15 character Ids against SQL Sales replicated 18 character Ids, you are recommended to force a case sensitive (CS) collation in the query.
For example:
select
sf_a.Id
,s.LegacyId
,sf_a.Name
from SourceData s
join Account sf_a
on s.LegacyId collate Latin1_General_CS_AS = left(sf_a.Id,15)
Here, the Sourcedata.LegacyId is a case sensitive 15 character Salesforce Id. Assuming your database collation is case insensitive (which is generally the case), it is unsafe to assume a simple left(Id,15) will reliably return a single match, as for example 0018d00000rRRGeAAO and 0018d00000rRRgeAAO are different Ids, yet left(Id,15) in a default case insensitive collation will regard them as the same string.
If you have a need to spot check the conversion from a 15 character to 18 character, refer to the ss_18 function which follows, although for performance reasons generally speaking the collation technique described here is recommended.
ss_18
15 to 18 Salesforce Id conversion
Simple function to return the case insensitive 18 character Salesforce Id from an inputted 15 character case sensitive Id.
Parameters
Parameter | Purpose |
@ui_id | nchar(15) inputted Salesforce 15 character Id |
Example of Use
select dbo.ss_18('0018d00000P8DFz')