Salesforce unique ids are only case insensitive when using the 18 character version with the three additional characters at the end which specify the case of all the previous characters. So when searching for and matching SFDC Ids in SQL Server using the 15 character ids, the result set will often include two or three matches where you were expecting only one.
The easiest way to enforce case sensitivity in a text search is to include the COLLATE clause (http://msdn.microsoft.com/en-us/library/ms184391.aspx) with the Latin1_General_CS_AS modifier.
For example, if I want to join two tables where one has the 15 character Id and the other has the 18 character Id, I would use the following script:
SELECT t1.Name, t2.Company
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.Id COLLATE Latin1_General_CS_AS = LEFT(t2.Id,15)
http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/
No comments:
Post a Comment