The other day, I had a problem with some data that I never dreamed I would ever see. In a case insensitive database, in a table’s column that was case insensitive, the customer was using the data as case sensitive. Firstly, let’s just go ahead and say it. “This was a sucky implementation.” But as is common, in my typical role as a data architect in the data warehousing team, I get to learn all sorts of interesting techniques for finding and dealing with “data” that has been used in “interesting” ways.<\/p>\n
What is kind of interesting is actually figuring out what that duplicated data was. The case that I was dealing with wasn’t a kind of useful packed surrogate value, where you may use a base 62 number, with a-z, A-Z and 0-9 as characters. So 1, 2, … , 9, 0, a, b, c, … x, y, z, A, B.. etc. 1A1 is a different value in that sequence than 1a1, and is greater . Neat technique, and one that I have been threatening to develop using a SEQUENCE object, where you can pack in a lot of sequential data in a small number of bytes. No, this wasn’t a useful case such as this, in this case, one value was lower case, another had leading capitals. So perhaps “active customer” and “Active Customer”. Yeah, seriously, they meant different things.<\/p>\n
Note:\u00a0The query I will use will help to find the permutations of values that you have in your data (Like “United States”, ‘UNITED STATES” for example.) Hence this is not a completely esoteric exercise.<\/em><\/p>\n To find the data, I figured I would use a case sensitive collation, but it isn’t as straightforward as grouping on the data using a collation. For a sample set of data, I will use the following:<\/p>\n Now, because it is case insensitive, to the normal user, you can’t really tell the difference, but in this contrived set, we will have > 1 row with the same color value (in my real example, there were thousands).<\/p>\n This returns:<\/p>\n Now, whether or not “beige” and “Beige” are bizarrely enough different things, or just misformatted data, this is not what we want back.\u00a0We want to treat them as different, so using a case sensitive collation (you need the COLLATE argument in the SELECT clause if you have it in the GROUP BY clause:<\/p>\n This doesn’t return anything. Hmm. The trick is in the HAVING clause. Use COUNT DISTINCT on the column as case sensitive instead of the GROUP BY and we get the items we want, but still just one per:<\/p>\n The output is the same as we had previously, with 2 rows per color (though this time they are all the lowercase items). However, now the ColorName column returned by the SELECT clause is case insensitive, so we can use it to get the case insensitive duplicates<\/p>\n Which returns the 6 rows with the duplicates we are hunting for:<\/p>\n With this set, you could then group on the values using the case sensitive collation to see how many duplicates you have, such as:<\/p>\n Easy enough, but hopefully if you get stuck doing this some day, this will help you get going.\n <\/p>\n","protected":false},"excerpt":{"rendered":" The other day, I had a problem with some data that I never dreamed I would ever see. In a case insensitive database, in a table’s column that was case insensitive, the customer was using the data as case sensitive. Firstly, let’s just go ahead and say it. “This was a sucky implementation.” But as……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[5134],"coauthors":[19684],"class_list":["post-80289","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80289","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=80289"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80289\/revisions"}],"predecessor-version":[{"id":80352,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80289\/revisions\/80352"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=80289"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=80289"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=80289"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=80289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}DROP TABLE IF EXISTS #Color\r\n --Note, because of the COLLATE argument, you must alias the column \r\nSELECT ColorID, ColorName COLLATE Latin1_General_CI_AS AS ColorName--Make sure column is case insensitive, no matter what your platform\r\nINTO #Color\r\nFROM WideWorldImporters.Warehouse.Colors\r\n\r\nINSERT INTO #Color(ColorID, ColorName)\r\nSELECT TOP 3 ColorID + 36, LOWER(ColorName) AS ColorName\r\nFROM #Color;<\/pre>\n
SELECT ColorName, COUNT(*)\r\nFROM #Color\r\nGROUP BY ColorName\r\nHAVING COUNT(*) > 1;<\/pre>\n
ColorName <\/code>
\n -------------------- -----------<\/code>
\n Azure\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 2<\/code>
\n beige\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 2<\/code>
\n Black\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 2<\/code><\/p>\nSELECT ColorName COLLATE Latin1_General_CS_AS, COUNT(*)\r\nFROM #Color\r\nGROUP BY ColorName COLLATE Latin1_General_CS_AS\r\nHAVING COUNT(*) > 1;<\/pre>\n
SELECT ColorName, COUNT(*)\r\nFROM #Color\r\nGROUP BY ColorName \r\nHAVING COUNT(DISTINCT ColorName COLLATE Latin1_General_CS_AS) > 1;<\/pre>\n
SELECT *\r\nFROM #Color\r\nWHERE ColorName IN ( SELECT ColorName\r\n FROM #Color\r\n GROUP BY ColorName \r\n HAVING COUNT(DISTINCT ColorName COLLATE Latin1_General_CS_AS) > 1\r\n );<\/pre>\n
ColorID\u00a0 \u00a0 \u00a0ColorName<\/code>
\n ----------- --------------------<\/code>
\n 1\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Azure<\/code>
\n 2\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Beige<\/code>
\n 3\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Black<\/code>
\n 37\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 azure<\/code>
\n 38\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 beige<\/code>
\n 39\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 black<\/code><\/p>\nSELECT ColorName COLLATE Latin1_General_CS_AS AS ColorName, COUNT(*) AS NumberOfUses\r\nFROM #Color\r\nWHERE ColorName IN ( SELECT ColorName\r\n FROM #Color\r\n GROUP BY ColorName \r\n HAVING COUNT(DISTINCT ColorName COLLATE Latin1_General_CS_AS) > 1\r\n )\r\nGROUP BY ColorName COLLATE Latin1_General_CS_AS;<\/pre>\n