Datatype for storing ip address in SQL Server
Sql ServerTsqlSql Server-2005TypesIp AddressSql Server Problem Overview
What datatype should I choose for storing an IP Address in a SQL Server?
By selecting the right datatype would it be easy enough to filter by IP address then?
Sql Server Solutions
Solution 1 - Sql Server
The technically correct way to store IPv4 is binary(4), since that is what it actually is (no, not even an INT32/INT(4), the numeric textual form that we all know and love (255.255.255.255) being just the display conversion of its binary content).
If you do it this way, you will want functions to convert to and from the textual-display format:
Here's how to convert the textual display form to binary:
CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
DECLARE @bin AS BINARY(4)
SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))
RETURN @bin
END
go
And here's how to convert the binary back to the textual display form:
CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @str AS VARCHAR(15)
SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
+ CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
+ CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
+ CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) );
RETURN @str
END;
go
Here's a demo of how to use them:
SELECT dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go
SELECT dbo.fnDisplayIPv4( 0xC04144C9 )
-- should return '192.65.68.201'
go
Finally, when doing lookups and compares, always use the binary form if you want to be able to leverage your indexes.
UPDATE:
I wanted to add that one way to address the inherent performance problems of scalar UDFs in SQL Server, but still retain the code-reuse of a function is to use an iTVF (inline table-valued function) instead. Here's how the first function above (string to binary) can be re-written as an iTVF:
CREATE FUNCTION dbo.itvfBinaryIPv4(@ip AS VARCHAR(15)) RETURNS TABLE
AS RETURN (
SELECT CAST(
CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))
AS BINARY(4)) As bin
)
go
Here's it in the example:
SELECT bin FROM dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go
And here's how you would use it in an INSERT
INSERT INTo myIpTable
SELECT {other_column_values,...},
(SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201'))
Solution 2 - Sql Server
You can use varchar. The length of IPv4 is static, but that of IPv6 may be highly variable.
Unless you have a good reason to store it as binary, stick with a string (textual) type.
Solution 3 - Sql Server
Here is some code to convert either IPV4 or IPv6 in varchar format to binary(16) and back. This is the smallest form I could think of. It should index well and provide a relatively easy way to filter on subnets. Requires SQL Server 2005 or later. Not sure it's totally bulletproof. Hope this helps.
-- SELECT dbo.fn_ConvertIpAddressToBinary('2002:1ff:6c2::1ff:6c2')
-- SELECT dbo.fn_ConvertIpAddressToBinary('10.4.46.2')
-- SELECT dbo.fn_ConvertIpAddressToBinary('bogus')
ALTER FUNCTION dbo.fn_ConvertIpAddressToBinary
(
@ipAddress VARCHAR(39)
)
RETURNS BINARY(16) AS
BEGIN
DECLARE
@bytes BINARY(16), @vbytes VARBINARY(16), @vbzone VARBINARY(2)
, @colIndex TINYINT, @prevColIndex TINYINT, @parts TINYINT, @limit TINYINT
, @delim CHAR(1), @token VARCHAR(4), @zone VARCHAR(4)
SELECT
@delim = '.'
, @prevColIndex = 0
, @limit = 4
, @vbytes = 0x
, @parts = 0
, @colIndex = CHARINDEX(@delim, @ipAddress)
IF @colIndex = 0
BEGIN
SELECT
@delim = ':'
, @limit = 8
, @colIndex = CHARINDEX(@delim, @ipAddress)
WHILE @colIndex > 0
SELECT
@parts = @parts + 1
, @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1)
SET @colIndex = CHARINDEX(@delim, @ipAddress)
IF @colIndex = 0
RETURN NULL
END
SET @ipAddress = @ipAddress + @delim
WHILE @colIndex > 0
BEGIN
SET @token = SUBSTRING(@ipAddress, @prevColIndex + 1, @Colindex - @prevColIndex - 1)
IF @delim = ':'
BEGIN
SET @zone = RIGHT('0000' + @token, 4)
SELECT
@vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(2)')
, @vbytes = @vbytes + @vbzone
IF @token = ''
WHILE @parts + 1 < @limit
SELECT
@vbytes = @vbytes + @vbzone
, @parts = @parts + 1
END
ELSE
BEGIN
SET @zone = SUBSTRING('' + master.sys.fn_varbintohexstr(CAST(@token AS TINYINT)), 3, 2)
SELECT
@vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(1)')
, @vbytes = @vbytes + @vbzone
END
SELECT
@prevColIndex = @colIndex
, @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1)
END
SET @bytes =
CASE @delim
WHEN ':' THEN @vbytes
ELSE 0x000000000000000000000000 + @vbytes
END
RETURN @bytes
END
-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x200201FF06C200000000000001FF06C2)
-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x0000000000000000000000000A0118FF)
ALTER FUNCTION [dbo].[fn_ConvertBinaryToIpAddress]
(
@bytes BINARY(16)
)
RETURNS VARCHAR(39) AS
BEGIN
DECLARE
@part VARBINARY(2)
, @colIndex TINYINT
, @ipAddress VARCHAR(39)
SET @ipAddress = ''
IF SUBSTRING(@bytes, 1, 12) = 0x000000000000000000000000
BEGIN
SET @colIndex = 13
WHILE @colIndex <= 16
SELECT
@part = SUBSTRING(@bytes, @colIndex, 1)
, @ipAddress = @ipAddress
+ CAST(CAST(@part AS TINYINT) AS VARCHAR(3))
+ CASE @colIndex WHEN 16 THEN '' ELSE '.' END
, @colIndex = @colIndex + 1
IF @ipAddress = '0.0.0.1'
SET @ipAddress = '::1'
END
ELSE
BEGIN
SET @colIndex = 1
WHILE @colIndex <= 16
BEGIN
SET @part = SUBSTRING(@bytes, @colIndex, 2)
SELECT
@ipAddress = @ipAddress
+ CAST('' as xml).value('xs:hexBinary(sql:variable("@part") )', 'varchar(4)')
+ CASE @colIndex WHEN 15 THEN '' ELSE ':' END
, @colIndex = @colIndex + 2
END
END
RETURN @ipAddress
END
Solution 4 - Sql Server
As I want to handle both IPv4 and IPv6, I am using VARBINARY(16) and the following SQL CLR functions to convert the text IP address presentation to bytes and the reverse:
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlBytes GetIPAddressBytesFromString (SqlString value)
{
IPAddress IP;
if (IPAddress.TryParse(value.Value, out IP))
{
return new SqlBytes(IP.GetAddressBytes());
}
else
{
return new SqlBytes();
}
}
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlString GetIPAddressStringFromBytes(SqlBytes value)
{
string output;
if (value.IsNull)
{
output = "";
}
else
{
IPAddress IP = new IPAddress(value.Value);
output = IP.ToString();
}
return new SqlString(output);
}
Solution 5 - Sql Server
For people using .NET can use IPAddress class to parse IPv4/IPv6 string and store it as a VARBINARY(16). Can use the same class to convert byte[] to string. If want to convert the VARBINARY in SQL:
--SELECT
-- dbo.varbinaryToIpString(CAST(0x7F000001 AS VARBINARY(4))) IPv4,
-- dbo.varbinaryToIpString(CAST(0x20010DB885A3000000008A2E03707334 AS VARBINARY(16))) IPv6
--ALTER
CREATE
FUNCTION dbo.varbinaryToIpString
(
@varbinaryValue VARBINARY(16)
)
RETURNS VARCHAR(39)
AS
BEGIN
IF @varbinaryValue IS NULL
RETURN NULL
IF DATALENGTH(@varbinaryValue) = 4
BEGIN
RETURN
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 1, 1))) + '.' +
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 2, 1))) + '.' +
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 3, 1))) + '.' +
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 4, 1)))
END
IF DATALENGTH(@varbinaryValue) = 16
BEGIN
RETURN
sys.fn_varbintohexsubstring(0, @varbinaryValue, 1, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 3, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 5, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 7, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 9, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 11, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 13, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 15, 2)
END
RETURN 'Invalid'
END
Solution 6 - Sql Server
sys.dm_exec_connections uses varchar(48) after SQL Server 2005 SP1. Sounds good enough for me especially if you want to use it compare to your value.
Realistically, you won't see IPv6 as mainstream for a while yet, so I'd prefer the 4 tinyint route. Saying that, I'm using varchar(48) because I have to use sys.dm_exec_connections...
Otherwise. Mark Redman's answer mentions a previous SO debate question.
Solution 7 - Sql Server
The following answer is based on answers by M. Turnhout and Jerry Birchler to this question but with the following improvements:
- Replaced the use of undocumented functions (
sys.fn_varbintohexsubstring,fn_varbintohexstr) withCONVERT()for binary styles - Replaced XML "hacks" (
CAST('' as xml).value('xs:hexBinary())) withCONVERT()for binary styles - Fixed bug in Jerry Birchler's implementation of
fn_ConvertIpAddressToBinary(as pointed out by C.Plock) - Add minor syntax sugar
The code has been tested in SQL Server 2014 and SQL Server 2016 (see test cases at the end)
IPAddressVarbinaryToString
Converts 4 bytes values to IPV4 and 16 byte values to IPV6 string representations. Note that this function does not shorten addresses.
ALTER FUNCTION dbo.IPAddressVarbinaryToString
(
@varbinaryValue VARBINARY( 16 )
)
RETURNS VARCHAR(39)
AS
BEGIN
IF @varbinaryValue IS NULL
RETURN NULL;
ELSE IF DATALENGTH( @varbinaryValue ) = 4
RETURN
CONVERT( VARCHAR(3), CONVERT(TINYINT, SUBSTRING( @varbinaryValue, 1, 1 ))) + '.' +
CONVERT( VARCHAR(3), CONVERT(TINYINT, SUBSTRING( @varbinaryValue, 2, 1 ))) + '.' +
CONVERT( VARCHAR(3), CONVERT(TINYINT, SUBSTRING( @varbinaryValue, 3, 1 ))) + '.' +
CONVERT( VARCHAR(3), CONVERT(TINYINT, SUBSTRING( @varbinaryValue, 4, 1 )));
ELSE IF DATALENGTH( @varbinaryValue ) = 16
RETURN
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 1, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 3, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 5, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 7, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 9, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 11, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 13, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 15, 2 ), 2 );
RETURN 'Invalid';
END
Test Cases:
SELECT dbo.IPAddressVarbinaryToString(0x00000000000000000000000000000000) -- 0000:0000:0000:0000:0000:0000:0000:0000 (no address shortening)
SELECT dbo.IPAddressVarbinaryToString(0x00010002000300400500060070000089) -- 0001:0002:0003:0040:0500:0600:7000:0089
SELECT dbo.IPAddressVarbinaryToString(0xC0A80148) -- 255.168.1.72
SELECT dbo.IPAddressVarbinaryToString(0x7F000001) -- 127.0.0.1 (no address shortening)
SELECT dbo.IPAddressVarbinaryToString(NULL) -- NULL
IPAddressStringToVarbinary
Converts IPV4 and IPV6 string representations to 4 byte and 16 bytes binary values respectively. Note that this function is able to parse most (all of the commonly used) of shorthand address representations (e.g. 127...1 and 2001:db8::1319:370:7348). To force this function to always return 16 byte binary values uncomment leading 0s concatenation at the end of the function.
ALTER FUNCTION [dbo].[IPAddressStringToVarbinary]
(
@IPAddress VARCHAR( 39 )
)
RETURNS VARBINARY(16) AS
BEGIN
IF @ipAddress IS NULL
RETURN NULL;
DECLARE @bytes VARBINARY(16), @token VARCHAR(4),
@vbytes VARBINARY(16) = 0x, @vbzone VARBINARY(2),
@tIPAddress VARCHAR( 40 ),
@colIndex TINYINT,
@delim CHAR(1) = '.',
@prevColIndex TINYINT = 0,
@parts TINYINT = 0, @limit TINYINT = 4;
-- Get position if IPV4 delimiter
SET @colIndex = CHARINDEX( @delim, @ipAddress );
-- If not IPV4, then assume IPV6
IF @colIndex = 0
BEGIN
SELECT @delim = ':', @limit = 8, @colIndex = CHARINDEX( @delim, @ipAddress );
-- Get number of parts (delimiters)
WHILE @colIndex > 0
SELECT @parts += 1, @colIndex = CHARINDEX( @delim, @ipAddress, @colIndex + 1 );
SET @colIndex = CHARINDEX( @delim, @ipAddress );
IF @colIndex = 0
RETURN NULL;
END
-- Add trailing delimiter (need new variable of larger size)
SET @tIPAddress = @IPAddress + @delim;
WHILE @colIndex > 0
BEGIN
SET @token = SUBSTRING( @tIPAddress, @prevColIndex + 1, @Colindex - @prevColIndex - 1 );
IF @delim = ':'
BEGIN
SELECT @vbzone = CONVERT( VARBINARY(2), RIGHT( '0000' + @token, 4 ), 2 ), @vbytes += @vbzone;
-- Handles consecutive sections of zeros representation rule (i.e. ::)(https://en.wikipedia.org/wiki/IPv6#Address_representation)
IF @token = ''
WHILE @parts + 1 < @limit
SELECT @vbytes += @vbzone, @parts += 1;
END
ELSE
BEGIN
SELECT @vbzone = CONVERT( VARBINARY(1), CONVERT( TINYINT, @token )), @vbytes += @vbzone
END
SELECT @prevColIndex = @colIndex, @colIndex = CHARINDEX( @delim, @tIPAddress, @colIndex + 1 )
END
SET @bytes =
CASE @delim
WHEN ':' THEN @vbytes
ELSE /*0x000000000000000000000000 +*/ @vbytes -- Return IPV4 addresses as 4 byte binary (uncomment leading 0s section to force 16 byte binary)
END
RETURN @bytes
END
Test Cases
Valid cases
SELECT dbo.IPAddressStringToVarbinary( '0000:0000:0000:0000:0000:0000:0000:0001' ) -- 0x0000000000000000000000000001 (check bug fix)
SELECT dbo.IPAddressStringToVarbinary( '0001:0002:0003:0040:0500:0600:7000:0089' ) -- 0x00010002000300400500060070000089
SELECT dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3:1319::370:7348' ) -- 0x20010DB885A308D31319000003707348 (check short hand)
SELECT dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3:1319:0000:370:7348' ) -- 0x20010DB885A308D31319000003707348
SELECT dbo.IPAddressStringToVarbinary( '192.168.1.72' ) -- 0xC0A80148
SELECT dbo.IPAddressStringToVarbinary( '127...1' ) -- 0x7F000001 (check short hand)
SELECT dbo.IPAddressStringToVarbinary( NULL ) -- NULL
SELECT dbo.IPAddressStringToVarbinary( '' ) -- NULL
-- Check that conversions return original address
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '0001:0002:0003:0040:0500:0600:7000:0089' )) -- '0001:0002:0003:0040:0500:0600:7000:0089'
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '127...1' )) -- 127.0.0.1
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '192.168.1.72' )) -- 192.168.1.72
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3:1319::370:7348' )) -- 2001:0db8:85a3:08d3:1319:0000:0370:7348
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3:1314:0000:370:7348' )) -- 2001:0db8:85a3:08d3:1319:0000:0370:7348
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3::370:7348' )) -- 2001:0DB8:85A3:08D3:0000:0000:0370:7348
-- This is technically an invalid IPV6 (according to Wikipedia) but it parses correctly
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8::1319::370:7348' )) -- 2001:0DB8:0000:0000:1319:0000:0370:7348
Invalid cases
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8::1319::7348' )) -- 2001:0DB8:0000:0000:0000:1319:0000:7348 (ambiguous address)
SELECT dbo.IPAddressStringToVarbinary( '127.1' ) -- 127.0.0.1 (not supported short-hand)
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '127.1' )) -- 127.0.0.1 (not supported short-hand)
SELECT dbo.IPAddressStringToVarbinary( '0300.0000.0002.0353' ) -- octal byte values
SELECT dbo.IPAddressStringToVarbinary( '0xC0.0x00.0x02.0xEB' ) -- hex values
SELECT dbo.IPAddressStringToVarbinary( 'C0.00.02.EB' ) -- hex values
Solution 8 - Sql Server
Thanks RBarry. I'm putting together an IP block allocation system and storing as binary is the only way to go.
I'm storing the CIDR representation (ex: 192.168.1.0/24) of the IP block in a varchar field, and using 2 calculated fields to hold the binary form of the start and end of the block. From there, I can run fast queries to see if a given block as already been allocated or is free to assign.
I modified your function to calculate the ending IP Address like so:
CREATE FUNCTION dbo.fnDisplayIPv4End(@block AS VARCHAR(18)) RETURNS BINARY(4)
AS
BEGIN
DECLARE @bin AS BINARY(4)
DECLARE @ip AS VARCHAR(15)
DECLARE @size AS INT
SELECT @ip = Left(@block, Len(@block)-3)
SELECT @size = Right(@block, 2)
SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))
SELECT @bin = CAST(@bin + POWER(2, 32-@size) AS BINARY(4))
RETURN @bin
END;
go
Solution 9 - Sql Server
I usually use a plain old VARCHAR filtering for an IPAddress works fine.
If you want to filter on ranges of IP address I'd break it into four integers.
Solution 10 - Sql Server
I like the functions of SandRock. But I found an error in the code of dbo.fn_ConvertIpAddressToBinary. The incoming parameter of @ipAddress VARCHAR(39) is too small when you concat the @delim to it.
SET @ipAddress = @ipAddress + @delim
You can increase it to 40. Or better yet use a new variable that is bigger and use that internally. That way you don't lose the last pair on large numbers.
SELECT dbo.fn_ConvertIpAddressToBinary('ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff')
Solution 11 - Sql Server
We do a lot of work where we need to figure out which IP's are within certain subnets. I've found that the simplest and most reliable way to do this is:
- Add a field to each table called IPInteger (bigint) (when invalid, set IP= '0.0.0.0')
- For smaller tables, I use a trigger that updates IPInteger on change
- For larger tables, I use a SPROC to refresh the IPIntegers
ALTER FUNCTION [dbo].[IP_To_INT ]
(
@IP CHAR(15)
)
RETURNS BIGINT
AS
BEGIN
DECLARE @IntAns BIGINT,
@block1 BIGINT,
@block2 BIGINT,
@block3 BIGINT,
@block4 BIGINT,
@base BIGINT
SELECT
@block1 = CONVERT(BIGINT, PARSENAME(@IP, 4)),
@block2 = CONVERT(BIGINT, PARSENAME(@IP, 3)),
@block3 = CONVERT(BIGINT, PARSENAME(@IP, 2)),
@block4 = CONVERT(BIGINT, PARSENAME(@IP, 1))
IF (@block1 BETWEEN 0 AND 255)
AND (@block2 BETWEEN 0 AND 255)
AND (@block3 BETWEEN 0 AND 255)
AND (@block4 BETWEEN 0 AND 255)
BEGIN
SET @base = CONVERT(BIGINT, @block1 * 16777216)
SET @IntAns = @base +
(@block2 * 65536) +
(@block3 * 256) +
(@block4)
END
ELSE
SET @IntAns = -1
RETURN @IntAns
END
Solution 12 - Sql Server
I'm using varchar(15) so far everything is working for me. Insert, Update, Select. I have just started an app that has IP Addresses, though I have not done much dev work yet.
Here is the select statement:
select * From dbo.Server
where [IP] = ('132.46.151.181')
Go