Re-purposing SQL Server PARSENAME For Splitting IPv4 Addresses
I stumbled across a very nice repurposing of the PARSENAME function in SQL Server recently while playing around with some GeoIP Data. In SQL Server, the PARSENAME function is used for working with fully qualified server objects. e.g. a table on a linked server ('LinkedServerName . Databasename . Ownername . TableName'). But PARSENAME can be used to easily split up any 4 token, dot delimited string into its constituent parts.
It's intended use, is for dealing with the string representations of server objects. e.g.
DECLARE @MyObject VARCHAR(100) SET @MyObject = 'MyServer.MyDB.MyOwner.MyTable' SELECT PARSENAME(@MyObject, 4) AS [ServerName], PARSENAME(@MyObject, 3) AS [DatabaseName], PARSENAME(@MyObject, 2) AS [OwnerName], PARSENAME(@MyObject, 1) As [TableName]
ServerName DatabaseName OwnerName TableName ----------- ---------------- ------------ ---------- MyServer MyDB MyOwner MyTable (1 row(s) affected)
A nice side effect of it's functionality, is that being useful for splitting any 4 part, dot delimited string into it's constiuent parts... e.g. an IPv4 Address; essentially saving you from performing charindex/substring gymnastics in order to split the string.
DECLARE @MyIP VARCHAR(15) SET @MyIP = '22.214.171.124' SELECT PARSENAME(@MyIP , 4) AS [octet1], PARSENAME(@MyIP , 3) AS [octet2], PARSENAME(@MyIP , 2) AS [octet3], PARSENAME(@MyIP , 1) As [octet4]
octet1 octet2 octet3 octet4 --------- --------- ------------------ 1 12 34 45 (1 row(s) affected)
If you're using the MaxMind GeoIP Lite Database, you can very quickly access each IP range by converting the IP address to it's equivalent Integer value and doing an indexed search for that value.
StringIP = oc4.oc3.oc2.oc1 IntIP = (oc4 * (256^3)) + (oc3 * (256^2)) + (oc2 * (256^1)) + (oc1 * (256^0))
Here's a sql function that'll do just that.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[IP_2_INT](@ip varchar(15)) RETURNS INT WITH SCHEMABINDING AS BEGIN DECLARE @ipInt INT, @oa TINYINT, @ob TINYINT, @oc TINYINT, @od TINYINT; SELECT @od = CAST(PARSENAME(@ip, 4) AS TINYINT), @oc = CAST(PARSENAME(@ip, 3) AS TINYINT), @ob = CAST(PARSENAME(@ip, 2) AS TINYINT), @oa = CAST(PARSENAME(@ip, 1) AS TINYINT) SET @ipInt = (@od*256*256*256) + (@oc*256*256) + (@ob*256) + @oa; RETURN @ipInt; END
There is a small downside to this. Unfortunately the PARSENAME function is non-deterministic meaning it could potentially return different results when called across different servers.
SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.IP_2_INT'), 'IsDeterministic')
This was incorrectly documented in that past and so if it#s an issue for you then there is a work around using good old charindex & substrings available on the microsoft connect bug report page for it.
PARSENAME incorrectly documented as deterministic