{"id":5211,"date":"2013-04-07T00:00:35","date_gmt":"2013-04-07T00:00:35","guid":{"rendered":"http:\/\/craftydba.com\/?p=5211"},"modified":"2017-10-11T16:24:14","modified_gmt":"2017-10-11T16:24:14","slug":"string-functions-difference","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=5211","title":{"rendered":"String Functions &#8211; DIFFERENCE()"},"content":{"rendered":"<p><a href=\"https:\/\/craftydba.com\/wp-content\/uploads\/2013\/04\/turquoise-yarn-md.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-thumbnail wp-image-5158\" title=\"turquoise-yarn-md\" src=\"https:\/\/craftydba.com\/wp-content\/uploads\/2013\/04\/turquoise-yarn-md-150x150.png\" alt=\"\" width=\"150\" height=\"150\" \/><\/a><br \/>\nI am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the DIFFERENCE() function today.<\/p>\n<p>The <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms188753.aspx\">DIFFERENCE()<\/a> function takes two string arguments and computes how different the two strings sound when spoken.  A return value of 4 means the strings sound the same while a value of 0 means the strings sound totally different.<\/p>\n<p>When using the difference function, you should look at the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms187384.aspx\">SOUNDEX()<\/a> function.  This function calculates a four-character code that is based on how the string sounds when spoken.<\/p>\n<p>The following two examples shows four different words representing animals.  One pair is very simular and one pair is very different.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"string functions - difference()\">\r\n-- Example returns 4, words are very close\r\nselect \r\n    soundex('Dog') as word_val1, \r\n    soundex('Dogs') as word_val2, \r\n    difference('Dog', 'Dogs') as how_close\r\n\r\n\r\n-- Example returns 0, words are very different\r\nselect \r\n    soundex('Rattle-Snake') as word_val1, \r\n    soundex('Mongoose') as word_val2, \r\n    difference('Rattle-Snake', 'Mongoose') as how_close\r\n\r\n<\/pre>\n<pre class=\"lang:TSQL theme:epicgeeks\" title=\"output\">\r\noutput: \r\n\r\nword_val1 word_val2 how_close\r\n--------- --------- -----------\r\nD200      D200      4\r\n\r\n\r\nword_val1 word_val2 how_close\r\n--------- --------- -----------\r\nR340      M522      0\r\n\r\n<\/pre>\n<p>One thing to always worry about when using TSQL functions is how will it react with a UNKNOWN or NULL values?<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"string functions - difference()\">\r\n-- Anything compared to NULL results in NULL\r\nselect difference(NULL, 'Mongoose') as how_close;\r\nselect difference('Mongoose', NULL) as how_close;\r\nselect difference(NULL, NULL) as how_close;\r\n\r\n<\/pre>\n<pre class=\"lang:TSQL theme:epicgeeks\" title=\"output\">\r\noutput: \r\n\r\nhow_close\r\n-----------\r\nNULL\r\n\r\n<\/pre>\n<p>Next time, I will be exploring the LEN() function.  <\/p>\n","protected":false},"excerpt":{"rendered":"<p>I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the DIFFERENCE() function today. The DIFFERENCE() function takes two string arguments and computes how different the two strings sound when spoken. A return value of 4 means the strings sound the same while a value of 0 means the strings sound totally different. When using the difference function, you should look at the SOUNDEX() function. This function calculates a four-character code that is based on how the string sounds&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[31,818,15,815,29],"class_list":["post-5211","post","type-post","status-publish","format-standard","hentry","category-very-short-articles","tag-database-developer","tag-difference","tag-john-f-miner-iii","tag-string-function","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5211","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5211"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5211\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5211"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5211"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5211"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}