{"id":52,"date":"2008-09-16T17:18:49","date_gmt":"2008-09-16T16:18:49","guid":{"rendered":"http:\/\/muratyaman.co.uk\/wp\/?p=52"},"modified":"2020-04-04T12:27:37","modified_gmt":"2020-04-04T11:27:37","slug":"ms-sql-anomalies","status":"publish","type":"post","link":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/2008\/09\/ms-sql-anomalies\/","title":{"rendered":"MS SQL Anomalies 3"},"content":{"rendered":"<p>For those who are still using ancient data types such as <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/aa258239(SQL.80).aspx\">CHAR <\/a>be careful if you are also using <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/aa258891(SQL.80).aspx\">REPLACE <\/a>function in MS SQL servers:<\/p>\n<p>Let&#8217;s test VARCHAR and CHAR fields on a simple table like the following:<\/p>\n<pre lang='sql'>\r\nCREATE TABLE [dbo].[test] (\r\n  [varchar5] varchar(5) COLLATE Latin1_General_BIN NOT NULL,\r\n  [char5] char(5) COLLATE Latin1_General_BIN NULL,\r\n  PRIMARY KEY CLUSTERED ([varchar5])\r\n)\r\nON [PRIMARY]\r\nGO\r\n\r\nselect varchar5, char5\r\n, right(char5, 3) as right3\r\n, replace(right(char5, 3),' ', '-') as replace_space_of_right3\r\n, replace(char5,' ', '-') as replace_space\r\nfrom test\r\n<\/pre>\n<p>The output is:<\/p>\n<p><a href='http:\/\/muratyaman.co.uk\/wp\/wp-content\/uploads\/2008\/09\/replace_function_on_char5.jpg'><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/muratyaman.co.uk\/wp\/wp-content\/uploads\/2008\/09\/replace_function_on_char5.jpg\" alt=\"Replace function on char field\" title=\"replace_function_on_char5\" width=\"500\" height=\"260\" class=\"aligncenter size-full wp-image-53\" srcset=\"https:\/\/www.muratyaman.co.uk\/blog\/wp-content\/uploads\/2008\/09\/replace_function_on_char5.jpg 551w, https:\/\/www.muratyaman.co.uk\/blog\/wp-content\/uploads\/2008\/09\/replace_function_on_char5-300x156.jpg 300w\" sizes=\"auto, (max-width: 500px) 100vw, 500px\" \/><\/a><\/p>\n<p>(version MS SQL Server 2000)<\/p>\n<p>As usual, Microsoft developers are full of surprises!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>For those who are still using ancient data types such as CHAR be careful if you are also using REPLACE function in MS SQL servers&#8230;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[56,33],"class_list":["post-52","post","type-post","status-publish","format-standard","hentry","category-technology","tag-ms-sql-server","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/52","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=52"}],"version-history":[{"count":3,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/52\/revisions"}],"predecessor-version":[{"id":1008,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/52\/revisions\/1008"}],"wp:attachment":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=52"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=52"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=52"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}