{"id":239,"date":"2018-11-18T18:00:05","date_gmt":"2018-11-18T18:00:05","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=239"},"modified":"2018-11-17T19:38:24","modified_gmt":"2018-11-17T19:38:24","slug":"in-sql-server-not-all-spaces-are-equal","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/","title":{"rendered":"In SQL Server, not all spaces are equal"},"content":{"rendered":"

I recently had a challenge mapping attributes using a lookup table. I had the same customer name in two tables “bobs widgets”, but when I tried to join on the names, there was no match.\u00a0To cut a long story short, there is more than one way to encode a space.<\/p>\n

This picks up from the recent article on\u00a0Cleaning Strings: Removing & replacing extra characters<\/a>\u00a0in that it looks at the same methods for character encoding. However in this case it’s a character with two different encodings but the same visual representation. Note, this issue also comes up with line breaks and some other characters, especially when working with unicode.<\/p>\n

The Encoding Problem<\/h1>\n

The Problem<\/h2>\n

There are two character encoding references for a space. The look the same on the screen, however their encodings are different and they will fail an equality test. This can be extra confusing with spaces as we are used to ignoring trailing spaces when checking string equality.<\/p>\n

PRINT 'Simple Select - They look the same';\r\n\r\nSELECT 'Bobs' + CHAR(32)  + 'Widgets' AS s_1,\r\n       'Bobs' + CHAR(160) + 'Widgets' AS s_2\r\n;\r\n\r\nGO\r\n\r\nPRINT 'Select encoded values: Where they are different';\r\n\r\nDECLARE @NAME_1 VARCHAR(100) = 'Bobs' + CHAR(32)  + 'Widgets';\r\nDECLARE @NAME_2 VARCHAR(100) = 'Bobs' + CHAR(160) + 'Widgets';\r\n\r\nSELECT @NAME_1 AS [name_1_coded], @NAME_2 AS [name_2_coded]\r\n WHERE @NAME_1 <> @NAME_2\r\n;\r\n\r\nGO\r\n\r\nPRINT 'Select encoded values: Where they are equal';\r\n\r\nDECLARE @NAME_1 VARCHAR(100) = 'Bobs' + CHAR(32)  + 'Widgets';\r\nDECLARE @NAME_2 VARCHAR(100) = 'Bobs' + CHAR(160) + 'Widgets';\r\n\r\n-- Note: No results\r\nSELECT @NAME_1 AS [name_1_coded], @NAME_2 AS [name_2_coded]\r\n WHERE @NAME_1 = @NAME_2\r\n;\r\n<\/pre>\n
This can be seen in action over at\u00a0rextester.com<\/a><\/p>\n
The update & Replace (to make uniform)<\/h1>\n
Similar to the extra characters article, the solution when finding rows in a table is to do a quick update:<\/p>\n
UPDATE t1\r\n   SET customer_name  = REPLACE(customer_name, CHAR(160), CHAR(32))\r\n WHERE customer_name <> REPLACE(customer_name, CHAR(160), CHAR(32))\r\n;\r\n<\/pre>\n
This can be seen in action at\u00a0rextester.com<\/a><\/p>\n
Replacing the values fixes the immediate problem. Note that it can fail if there is a primary key\/unique index on a column which has both variations in it.<\/p>\n
Prevention Is Better Than The Cure<\/h1>\n
Some programmatic methods to solve this issue are.<\/p>\n
Perform the conversion on insert\/update<\/h2>\n
If a stored procedure performs the insert or update, then add a conversion to the procedure to clean all new values going into a table.<\/p>\n
INSERT\r\n  INTO t1 (customer_name)\r\nVALUES(REPLACE('Bobs' + CHAR(160) + 'Widgets',\r\n               CHAR(160),\r\n               CHAR(32)\r\n              )\r\n      )\r\n;<\/pre>\n
A Constraint<\/h2>\n
Use of a constraint would prevent the unwanted character from going into the column (this causes an error message\/failure).<\/p>\n
A Trigger<\/h2>\n
A trigger can be a handy way to clean data, configured to update values on insert\/update so that the DBMS attempts to “fix” things. It applies the same idea as modifying the insert\/update statement, but performs it programmatically for each record. Note, it could have performance issues with large volumes of data.<\/p>\n
This is helpful when you have less technical users providing\/inserting updates. It should work silently.<\/p>\n","protected":false},"excerpt":{"rendered":"
I recently had a challenge mapping attributes using a lookup table. I had the same customer name in two tables “bobs widgets”, but when I tried to join on the names, there was no match.\u00a0To cut a long story short, there is more than one way to encode a space. This picks up from the…<\/p>\n
Read More  Read More<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4],"tags":[],"class_list":["post-239","post","type-post","status-publish","format-standard","hentry","category-databases","category-sql-server"],"yoast_head":"\nIn SQL Server, not all spaces are equal - Rows Across The Lake<\/title>\n<meta name=\"description\" content=\"I recently had a challenge mapping attributes using a lookup table. I had the same name in two tables, but when joining on the names, there was no match.\u00a0To cut a long story short, there is more than one way to encode a space.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"In SQL Server, not all spaces are equal - Rows Across The Lake\" \/>\n<meta property=\"og:description\" content=\"I recently had a challenge mapping attributes using a lookup table. I had the same name in two tables, but when joining on the names, there was no match.\u00a0To cut a long story short, there is more than one way to encode a space.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/\" \/>\n<meta property=\"og:site_name\" content=\"Rows Across The Lake\" \/>\n<meta property=\"article:published_time\" content=\"2018-11-18T18:00:05+00:00\" \/>\n<meta name=\"author\" content=\"david\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:site\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"david\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/11\\\/18\\\/in-sql-server-not-all-spaces-are-equal\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/11\\\/18\\\/in-sql-server-not-all-spaces-are-equal\\\/\"},\"author\":{\"name\":\"david\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"headline\":\"In SQL Server, not all spaces are equal\",\"datePublished\":\"2018-11-18T18:00:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/11\\\/18\\\/in-sql-server-not-all-spaces-are-equal\\\/\"},\"wordCount\":386,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"articleSection\":[\"Databases\",\"SQL Server\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/11\\\/18\\\/in-sql-server-not-all-spaces-are-equal\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/11\\\/18\\\/in-sql-server-not-all-spaces-are-equal\\\/\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/11\\\/18\\\/in-sql-server-not-all-spaces-are-equal\\\/\",\"name\":\"In SQL Server, not all spaces are equal - Rows Across The Lake\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\"},\"datePublished\":\"2018-11-18T18:00:05+00:00\",\"description\":\"I recently had a challenge mapping attributes using a lookup table. I had the same name in two tables, but when joining on the names, there was no match.\u00a0To cut a long story short, there is more than one way to encode a space.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/11\\\/18\\\/in-sql-server-not-all-spaces-are-equal\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/11\\\/18\\\/in-sql-server-not-all-spaces-are-equal\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/11\\\/18\\\/in-sql-server-not-all-spaces-are-equal\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"In SQL Server, not all spaces are equal\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\",\"name\":\"Rows Across The Lake\",\"description\":\"Data & Databases\",\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\",\"name\":\"david\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"caption\":\"david\"},\"logo\":{\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"In SQL Server, not all spaces are equal - Rows Across The Lake","description":"I recently had a challenge mapping attributes using a lookup table. I had the same name in two tables, but when joining on the names, there was no match.\u00a0To cut a long story short, there is more than one way to encode a space.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/","og_locale":"en_GB","og_type":"article","og_title":"In SQL Server, not all spaces are equal - Rows Across The Lake","og_description":"I recently had a challenge mapping attributes using a lookup table. I had the same name in two tables, but when joining on the names, there was no match.\u00a0To cut a long story short, there is more than one way to encode a space.","og_url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/","og_site_name":"Rows Across The Lake","article_published_time":"2018-11-18T18:00:05+00:00","author":"david","twitter_card":"summary_large_image","twitter_creator":"@d_roman_h","twitter_site":"@d_roman_h","twitter_misc":{"Written by":"david","Estimated reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/#article","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/"},"author":{"name":"david","@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"headline":"In SQL Server, not all spaces are equal","datePublished":"2018-11-18T18:00:05+00:00","mainEntityOfPage":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/"},"wordCount":386,"commentCount":0,"publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"articleSection":["Databases","SQL Server"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/","url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/","name":"In SQL Server, not all spaces are equal - Rows Across The Lake","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/#website"},"datePublished":"2018-11-18T18:00:05+00:00","description":"I recently had a challenge mapping attributes using a lookup table. I had the same name in two tables, but when joining on the names, there was no match.\u00a0To cut a long story short, there is more than one way to encode a space.","breadcrumb":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/11\/18\/in-sql-server-not-all-spaces-are-equal\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/datablog.roman-halliday.com\/"},{"@type":"ListItem","position":2,"name":"In SQL Server, not all spaces are equal"}]},{"@type":"WebSite","@id":"https:\/\/datablog.roman-halliday.com\/#website","url":"https:\/\/datablog.roman-halliday.com\/","name":"Rows Across The Lake","description":"Data & Databases","publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/datablog.roman-halliday.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b","name":"david","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","caption":"david"},"logo":{"@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g"}}]}},"_links":{"self":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/239","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/comments?post=239"}],"version-history":[{"count":2,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/239\/revisions"}],"predecessor-version":[{"id":242,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/239\/revisions\/242"}],"wp:attachment":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/media?parent=239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/categories?post=239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/tags?post=239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}