Microsoft SQL Server string compare case sensitive

Sometimes, in your collections of data might contains case sensitive values. In my case, there are several products that have same Manufacturer but our client misused that data and created duplicated “same” manufacturers without verifying if they are exists in the system or not. It leads to an issue that the product might load incorrect manufacturer every time the application load its detail. The relationship between product and manufacturer changes too often because of below SQL Script:

/**
* This query returns all products that exists in both 'EERO' and 'eero' manufacturer
**/
DECLARE @ManufacturerName NVARCHAR(255);
SET @ManufacturerName = 'EERO' -- This name is duplicated with 'eero'
SELECT p.* 
FROM products p
INNER JOIN manufacturers m on m.id = p.manufacturer_id
WHERE m.name = @ManufacturerName -- We only need to get which products that have manufacturer of 'EERO' not 'eero'
DECLARE @ManufacturerName NVARCHAR(255);
SET @ManufacturerName = 'EERO' -- This name is duplicated with 'eero'
SELECT p.* 
FROM products p
INNER JOIN manufacturers m on m.id = p.manufacturer_id
WHERE m.name = @ManufacturerName COLLATE Latin1_General_CS_AS -- case sensitive comparison

Notice: Trying to get property 'secret' of non-object in /home/blog.beehexa.com/deploy/releases/20191228082638/wp-content/plugins/jetpack/modules/comments/comments.php on line 281

Notice: Trying to get property 'secret' of non-object in /home/blog.beehexa.com/deploy/releases/20191228082638/wp-content/plugins/jetpack/modules/comments/comments.php on line 299

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.