Check constraint to validate IP address field

I'm working on a project involving C# and开发者_StackOverflow中文版 a SQL Server 2008 database. In one of the tables, I have a field (nvarchar(15)) which will contain an IP address.

I'd like to add a check constraint which will validate that the input value is actually an IP address.

I wanted to use a regex to do that, but it seems that this feature is not supported by default. I saw things about writing a customm dll with UDF inside (MSDN tutorial), but I don't really understand how it works (i.e. where should I place the dll ?)

Is there a "simple" way to add such a constraint ? Any solution is welcome.

Thanks in advance !

There are several way of doing this - the most performant one would probably be a CLR function in the database.

This is because SQL has fairly poor text manipulation tooling and no native RegEx in SQL Server.

As other have said, this is better handled by an application before insertion to the DB.

It shouldn't be handled in the database, it should be handled first and foremost in the application.

There's no harm in then adding a check to the database, but leaving it up to the DB to filter input is very sketchy.

The easiest way I can think of is to create a function like fnCheckIP and use this function in the constraint.

There's no need to use UDF.

create function fnCheckIP(@ip varchar(15)) returns bit
AS
begin
    if (@ip is null)
        return null

    declare @num1 int
    declare @num varchar(15)    
    declare @pos int
    while (@ip is not null)
    begin
        set @pos = IsNull(NullIf(charindex('.', @ip), 0), Len(@ip) + 1)
        set @num = substring(@ip, 1, @pos - 1)

        if (isnumeric(@num) = 0) or (not cast(@num as int) between 0 and 255)
            return cast(0 as bit)

        if (len(@ip) - @pos <= 0)
            set @ip = null
        else        
            set @ip = NullIf(substring(@ip, @pos + 1, len(@ip) - @pos), '')
    end

    return cast (1 as bit)
end
go

select dbo.fnCheckIP('127.0.0.1')
select dbo.fnCheckIP('127.0.0.300')

This solution is similar to Paulo's but using either approach will require getting rid of the comma character because isnumeric allows commas which will throw a cast to int error.

CREATE FUNCTION fn_ValidateIP
(
    @ip varchar(255)
)
RETURNS int
AS
BEGIN
    DECLARE @Result int = 0
        IF
            @ip not like '%,%' and
            len(@ip) <= 15 and
            isnumeric(PARSENAME(@ip,4)) = 1 and
            isnumeric(PARSENAME(@ip,3)) = 1 and
            isnumeric(PARSENAME(@ip,2)) = 1 and
            isnumeric(PARSENAME(@ip,1)) = 1 and
            cast(PARSENAME(@ip,4) as int) between 1 and 255 and
            cast(PARSENAME(@ip,3) as int) between 0 and 255 and
            cast(PARSENAME(@ip,2) as int) between 0 and 255 and
            cast(PARSENAME(@ip,1) as int) between 0 and 255
            set @Result = 1
        ELSE
            set @Result = 0
    RETURN @Result
END

select dbo.fn_ValidateIP('127.0.0.1')

This may not be entirely practical, but one way would be to store the converted string ###-###-###-### into a binary(4) data type. Let the interface fuss around with hyphens and deal with converting the four numbers to binary and back (and this could probably even be done by a caluclated column.) A bit extreme, yes, but with binary(4) you will always be able to turn it into an IP address.

At last about 10 yrs after Oracle, sqlserver got native compilation (with limitations)

    ALTER function fn_ValidateIPv4
(
@ip varchar(255)
)
RETURNS int
--WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION  
AS
BEGIN 
--ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
/* only sql2016 native Compilation **/
DECLARE @len_ip as int;
SET @len_ip =  len(@ip);
DECLARE @firstBlock varchar(4) = '';
DECLARE @secondBlock varchar(4) = '';
DECLARE @thirdBlock varchar(4) = '';
DECLARE @fourthBlock varchar(4) = '';
DECLARE @countDot as smallint = 0;
DECLARE @l_i as smallint = 0;

DECLARE @l_curChar varchar(1) = 'X';

DECLARE @Result int = 0
    IF (@len_ip <= 15)
    BEGIN
        WHILE (@l_i < @len_ip) 
        BEGIN
            set @l_i += 1;
            set @l_curChar = substring(@ip,@l_i,1);
            if @l_curChar = '.'
                SET @countDot += 1
            ELSE
            BEGIN
                IF @l_curChar IN ( '0','1','2','3','4','5','6','7','8','9' )
                BEGIN
                    IF @countDot = 0 
                        SET @firstBlock = @firstBlock + @l_curChar;
                    IF @countDot = 1
                        SET @secondBlock = @secondBlock + @l_curChar;
                    IF @countDot = 2
                        SET @thirdBlock = @thirdBlock + @l_curChar;
                    IF @countDot = 3
                        SET @fourthBlock = @fourthBlock + @l_curChar;
                    IF  @countDot > 3
                        set @firstBlock = 'AAA'; -- force error 
                END
                ELSE set @firstBlock = 'AAA'; -- force error                

            END;
        END;            

        IF ( @countDot = 3 and
            cast(@fourthBlock as int) between 1 and 255 and
            cast(@thirdBlock as int) between 0 and 255 and
            cast(@secondBlock as int) between 0 and 255 and
            cast(@firstBlock as int) between 0 and 255 
            )           
            set @Result = 1;
    END;

    /*
    select dbo.fn_ValidateIPv4( '127.0.0.258' );        
    */
RETURN @Result
END;

I had to remove not de-supported built functions isnumeric etc...