String splitting using sql operator (ip address)

Question

I need to split the IP address in sql.

I found a lot, but could not find a built-in method that performs this task.

How can I complete this task?

I am using sql server

Answer 1

You can use the PARSENAME function as follows:

with address as( select '192.168.1.1' as IpAddress Union select '192.168.1.2' as IpAddress Union select '192.168.1.3' as IpAddress ) SELECT PARSENAME(IpAddress,4) as first, PARSENAME(IpAddress,3) as second, PARSENAME(IpAddress,2) as third, PARSENAME(IpAddress,1) as fourth, FROM address 

The PARSENAME function returns the specified part of the object name.

Answer 2

I found it useful to convert IPV4 addresses from dotted string notation to a (large) integer so that they can be stored, compared, .... (Note that the following function does not validate input.)

 create function [dbo].[IntegerIPV4Address]( @IPV4Address VarChar(16) ) returns BigInt with SchemaBinding -- Deterministic function. begin -- NB: ParseName is non-deterministic. declare @Dot1 as Int = CharIndex( '.', @IPV4Address ); declare @Dot2 as Int = CharIndex( '.', @IPV4Address, @Dot1 + 1 ); declare @Dot3 as Int = CharIndex( '.', @IPV4Address, @Dot2 + 1 ); return Cast( Substring( @IPV4Address, 0, @Dot1 ) as BigInt ) * 0x1000000 + Cast( Substring( @IPV4Address, @Dot1 + 1, @Dot2 - @Dot1 - 1 ) as BigInt ) * 0x10000 + Cast( Substring( @IPV4Address, @Dot2 + 1, @Dot3 - @Dot2 - 1 ) as BigInt ) * 0x100 + Cast( Substring( @IPV4Address, @Dot3 + 1, Len( @IPV4Address ) * 1 ) as BigInt ); end 

Convert back to a dotted line filled with zeros (so that alpha behavior behaves well):

 create function [dbo].[NormalizedIPV4Address]( @IntegerIPV4Address as BigInt ) returns VarChar(16) with SchemaBinding -- Deterministic function. begin declare @BinaryAddress as VarBinary(4) = Cast( @IntegerIPV4Address as VarBinary(4) ); return Right( '00' + Cast( Cast( Substring( @BinaryAddress, 1, 1 ) as Int ) as VarChar(3) ), 3 ) + '.' + Right( '00' + Cast( Cast( Substring( @BinaryAddress, 2, 1 ) as Int ) as VarChar(3) ), 3 ) + '.' + Right( '00' + Cast( Cast( Substring( @BinaryAddress, 3, 1 ) as Int ) as VarChar(3) ), 3 ) + '.' + Right( '00' + Cast( Cast( Substring( @BinaryAddress, 4, 1 ) as Int ) as VarChar(3) ), 3 ) end