{"id":946,"date":"2013-04-21T13:28:20","date_gmt":"2013-04-21T13:28:20","guid":{"rendered":"http:\/\/andrewpallant.ca\/wordpress\/?p=946"},"modified":"2013-11-26T11:46:22","modified_gmt":"2013-11-26T16:46:22","slug":"maxmind-geoip-look-up-for-microsoft-sql","status":"publish","type":"post","link":"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/","title":{"rendered":"MaxMind GEOIP Look-up for Microsoft SQL"},"content":{"rendered":"<p>Recently I implemented a GEOIP solution on a client site; however, the site was built on a Microsoft SQL database engine and I could not find a solution for looking up the country name easily. \u00a0Through some quick Google searches I had tripped on the site for which I started at ( http:\/\/dev.maxmind.com\/geoip\/csv\u00a0). \u00a0The site <a title=\"GEOIP Solutions\" href=\"http:\/\/www.maxmind.com\" target=\"_blank\">maxmind.com<\/a>\u00a0gave me the MySQL solution and the mathematical solution for creating the integer. \u00a0Based on this\u00a0information, I created my own function in MS SQL to retrieve me the calculated IP integer for easier use.<\/p>\n<p><!--more--><\/p>\n<pre style=\"background-color: f8fcc7; margin-left: 20px; padding: 10px;\">SET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n-- =============================================\r\n-- Author: Andrew Pallant ( www.ldndeveloper.com )\r\n-- Create date: 19 \/ 04 \/ 2013\r\n-- Description: Used as a reference: http:\/\/dev.maxmind.com\/geoip\/csv\r\n-- =============================================\r\nCREATE FUNCTION fetchCountryINT\r\n(\r\n@IP as varchar(30)\r\n)\r\nRETURNS numeric(16,0)\r\nAS\r\nBEGIN\r\ndeclare @o1 numeric(16,0)\r\ndeclare @o2 numeric(16,0)\r\ndeclare @o3 numeric(16,0)\r\ndeclare @o4 numeric(16,0)\r\ndeclare @CountryInt numeric (16,0)\r\ndeclare @LastIndex int\r\n\r\n-- Get 1st Segment\r\nset @LastIndex = CHARINDEX('.',@IP)\r\nselect @o1 = SUBSTRING(@IP,1, @LastIndex-1)\r\n\r\n-- Get 2nd Segment\r\nset @IP = SUBSTRING(@IP,@LastIndex+1, LEN(@IP) - @LastIndex)\r\nset @LastIndex = CHARINDEX('.',@IP)\r\nselect @o2 = SUBSTRING(@IP,1, @LastIndex-1)\r\n\r\n-- Get 3rd Segment\r\nset @IP = SUBSTRING(@IP,@LastIndex+1, LEN(@IP) - @LastIndex)\r\nset @LastIndex = CHARINDEX('.',@IP)\r\nselect @o3 = SUBSTRING(@IP,1, @LastIndex-1)\r\n\r\n-- Get 4th Segment\r\nset @IP = SUBSTRING(@IP,@LastIndex+1, LEN(@IP) - @LastIndex)\r\nset @LastIndex = CHARINDEX('.',@IP)\r\nselect @o4 = @IP\r\n\r\n-- Calculate Integer\r\nselect @CountryInt = (@o1 * 16777216 ) + (@o2 * 65536) + (@o3 * 256) + @o4\r\n\r\nRETURN @CountryInt\r\nEND\r\nGO<\/pre>\n<p>How to use the function in a SQL statement:<\/p>\n<pre style=\"background-color: f8fcc7; margin-left: 20px; padding: 10px;\">select country_name from GEOIP where dbo.fetchCountryINT('174.36.207.186') between begin_ip_num AND end_ip_num<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Recently I implemented a GEOIP solution on a client site; however, the site was built on a Microsoft SQL database engine and I could not find a solution for looking up the country name easily. \u00a0Through some quick Google searches I had tripped on the site for which I started at ( http:\/\/dev.maxmind.com\/geoip\/csv\u00a0). \u00a0The site &hellip; <a href=\"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">MaxMind GEOIP Look-up for Microsoft SQL<\/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":[141,36,78,96],"tags":[246],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v23.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>MaxMind GEOIP Look-up for Microsoft SQL - LDNDeveloper<\/title>\r\n<meta name=\"description\" content=\"Microsoft SQL Function to Calculate the IP Integeger for MaxMind&#039;s GEOIP Solution\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"MaxMind GEOIP Look-up for Microsoft SQL - LDNDeveloper\" \/>\r\n<meta property=\"og:description\" content=\"Microsoft SQL Function to Calculate the IP Integeger for MaxMind&#039;s GEOIP Solution\" \/>\r\n<meta property=\"og:url\" content=\"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/\" \/>\r\n<meta property=\"og:site_name\" content=\"LDNDeveloper\" \/>\r\n<meta property=\"article:published_time\" content=\"2013-04-21T13:28:20+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2013-11-26T16:46:22+00:00\" \/>\r\n<meta name=\"author\" content=\"ldnDeveloper\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@LdnDeveloper\" \/>\r\n<meta name=\"twitter:site\" content=\"@LdnDeveloper\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ldnDeveloper\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/#article\",\"isPartOf\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/\"},\"author\":{\"name\":\"ldnDeveloper\",\"@id\":\"https:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84\"},\"headline\":\"MaxMind GEOIP Look-up for Microsoft SQL\",\"datePublished\":\"2013-04-21T13:28:20+00:00\",\"dateModified\":\"2013-11-26T16:46:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/\"},\"wordCount\":111,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84\"},\"keywords\":[\"SQL\"],\"articleSection\":[\"Developement\",\"How To\",\"SQL\",\"Web\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/\",\"url\":\"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/\",\"name\":\"MaxMind GEOIP Look-up for Microsoft SQL - LDNDeveloper\",\"isPartOf\":{\"@id\":\"https:\/\/andrewpallant.ca\/wordpress\/#website\"},\"datePublished\":\"2013-04-21T13:28:20+00:00\",\"dateModified\":\"2013-11-26T16:46:22+00:00\",\"description\":\"Microsoft SQL Function to Calculate the IP Integeger for MaxMind's GEOIP Solution\",\"breadcrumb\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/andrewpallant.ca\/wordpress\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MaxMind GEOIP Look-up for Microsoft SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/andrewpallant.ca\/wordpress\/#website\",\"url\":\"https:\/\/andrewpallant.ca\/wordpress\/\",\"name\":\"LDNDeveloper\",\"description\":\"Learning, Growing and Sharing.\",\"publisher\":{\"@id\":\"https:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/andrewpallant.ca\/wordpress\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84\",\"name\":\"ldnDeveloper\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/andrewpallant.ca\/wordpress\/wp-content\/uploads\/2017\/05\/cropped-AAEAAQAAAAAAAAXQAAAAJDQxMGRlMzFjLWM4ODctNDk1NC05M2EyLWE1NDNhNTRiZjVlYw-2.jpg\",\"contentUrl\":\"https:\/\/andrewpallant.ca\/wordpress\/wp-content\/uploads\/2017\/05\/cropped-AAEAAQAAAAAAAAXQAAAAJDQxMGRlMzFjLWM4ODctNDk1NC05M2EyLWE1NDNhNTRiZjVlYw-2.jpg\",\"width\":512,\"height\":512,\"caption\":\"ldnDeveloper\"},\"logo\":{\"@id\":\"https:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/\"},\"description\":\"Andrew Pallant (@LdnDeveloper) has been a web, database and desktop developer for over 16 years. Andrew has worked on projects that ranged from factory automation to writing business applications. Most recently he has been heavily involved in various forms for ecommerce projects. Over the years Andrew has worn many hats: Project Manager, IT Manager, Lead Developer, Supervisor of Developers and many more - See more at: http:\/\/www.unlatched.com\/#sthash.8DiTkpKy.dpuf\",\"sameAs\":[\"http:\/\/www.andrewpallant.ca\",\"https:\/\/x.com\/LdnDeveloper\"],\"url\":\"http:\/\/andrewpallant.ca\/wordpress\/author\/ldndeveloper\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MaxMind GEOIP Look-up for Microsoft SQL - LDNDeveloper","description":"Microsoft SQL Function to Calculate the IP Integeger for MaxMind's GEOIP Solution","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":"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/","og_locale":"en_US","og_type":"article","og_title":"MaxMind GEOIP Look-up for Microsoft SQL - LDNDeveloper","og_description":"Microsoft SQL Function to Calculate the IP Integeger for MaxMind's GEOIP Solution","og_url":"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/","og_site_name":"LDNDeveloper","article_published_time":"2013-04-21T13:28:20+00:00","article_modified_time":"2013-11-26T16:46:22+00:00","author":"ldnDeveloper","twitter_card":"summary_large_image","twitter_creator":"@LdnDeveloper","twitter_site":"@LdnDeveloper","twitter_misc":{"Written by":"ldnDeveloper","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/#article","isPartOf":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/"},"author":{"name":"ldnDeveloper","@id":"https:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84"},"headline":"MaxMind GEOIP Look-up for Microsoft SQL","datePublished":"2013-04-21T13:28:20+00:00","dateModified":"2013-11-26T16:46:22+00:00","mainEntityOfPage":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/"},"wordCount":111,"commentCount":0,"publisher":{"@id":"https:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84"},"keywords":["SQL"],"articleSection":["Developement","How To","SQL","Web"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/","url":"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/","name":"MaxMind GEOIP Look-up for Microsoft SQL - LDNDeveloper","isPartOf":{"@id":"https:\/\/andrewpallant.ca\/wordpress\/#website"},"datePublished":"2013-04-21T13:28:20+00:00","dateModified":"2013-11-26T16:46:22+00:00","description":"Microsoft SQL Function to Calculate the IP Integeger for MaxMind's GEOIP Solution","breadcrumb":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/andrewpallant.ca\/wordpress\/maxmind-geoip-look-up-for-microsoft-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/andrewpallant.ca\/wordpress\/"},{"@type":"ListItem","position":2,"name":"MaxMind GEOIP Look-up for Microsoft SQL"}]},{"@type":"WebSite","@id":"https:\/\/andrewpallant.ca\/wordpress\/#website","url":"https:\/\/andrewpallant.ca\/wordpress\/","name":"LDNDeveloper","description":"Learning, Growing and Sharing.","publisher":{"@id":"https:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/andrewpallant.ca\/wordpress\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84","name":"ldnDeveloper","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/","url":"https:\/\/andrewpallant.ca\/wordpress\/wp-content\/uploads\/2017\/05\/cropped-AAEAAQAAAAAAAAXQAAAAJDQxMGRlMzFjLWM4ODctNDk1NC05M2EyLWE1NDNhNTRiZjVlYw-2.jpg","contentUrl":"https:\/\/andrewpallant.ca\/wordpress\/wp-content\/uploads\/2017\/05\/cropped-AAEAAQAAAAAAAAXQAAAAJDQxMGRlMzFjLWM4ODctNDk1NC05M2EyLWE1NDNhNTRiZjVlYw-2.jpg","width":512,"height":512,"caption":"ldnDeveloper"},"logo":{"@id":"https:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/"},"description":"Andrew Pallant (@LdnDeveloper) has been a web, database and desktop developer for over 16 years. Andrew has worked on projects that ranged from factory automation to writing business applications. Most recently he has been heavily involved in various forms for ecommerce projects. Over the years Andrew has worn many hats: Project Manager, IT Manager, Lead Developer, Supervisor of Developers and many more - See more at: http:\/\/www.unlatched.com\/#sthash.8DiTkpKy.dpuf","sameAs":["http:\/\/www.andrewpallant.ca","https:\/\/x.com\/LdnDeveloper"],"url":"http:\/\/andrewpallant.ca\/wordpress\/author\/ldndeveloper\/"}]}},"_links":{"self":[{"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/posts\/946"}],"collection":[{"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/comments?post=946"}],"version-history":[{"count":5,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/posts\/946\/revisions"}],"predecessor-version":[{"id":1102,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/posts\/946\/revisions\/1102"}],"wp:attachment":[{"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/media?parent=946"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/categories?post=946"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/tags?post=946"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}