How to validate the below format in SQL SERVER 2005(SET BASED)

How to restrict t开发者_运维知识库he length? Like 12345.789.22.7890 is invalid because the IP ranges from 0-255

Kindly help me

Why not store the IP address as 4 256-bit elements (Byte type) along with a string repr? Sure, it sort of wastes space, but it allows you to write simpler queries, does not it?

The best answer I have found in T-SQL so far is using the rules outlined in a blog article. I used these rules in a function that validates IP addresses before converting them to big integers so that I could lookup the country associated with the IP address. This was helpful since I had no control over the input values for the IP address, so I couldn't enforce data integrity constraints on the source system. Below is a copy of the function modified so that it returns 1 if the IP address is valid and 0 if it is invalid.

CREATE FUNCTION dbo.udf_ValidateIPAddress
    (@InputString VARCHAR(8000))
RETURNS TINYINT
AS
BEGIN

    DECLARE @Return BIGINT = IIF(@InputString LIKE '%_.%_.%_.%_'  -- 3 periods and no empty octets 
        AND @InputString NOT LIKE '%.%.%.%.%'  -- not 4 periods or more 
        AND @InputString NOT LIKE '%[^0-9.]%'  -- no characters other than digits and periods 
        AND @InputString NOT LIKE '%[0-9][0-9][0-9][0-9]%'  -- not more than 3 digits per octet 
        AND @InputString NOT LIKE '%[3-9][0-9][0-9]%'  -- NOT 300 - 999 
        AND @InputString NOT LIKE '%2[6-9][0-9]%'  -- NOT 260 - 299 
        AND @InputString NOT LIKE '%25[6-9]%'  -- NOT 256 - 259 
        , 1, 0)

    RETURN @Return;

END

This you can achieve using PARSENAME function of SQL Server. Below is a full demo for this: Let’s first create one table and define CHECK Constraint using PARSENAME.

CREATE TABLE tbl_IPAddress
(
 IPAddress VARCHAR(15)
)
GO

ALTER TABLE tbl_IPAddress ADD CONSTRAINT chk_tbl_IPAddress_IPAddress
CHECK 
(
 (ParseName(IPAddress, 4) = 12) 
 AND (ParseName(IPAddress, 3) = 4) 
 AND (ParseName(IPAddress, 2) BETWEEN 8 AND 26) 
 AND (ParseName(IPAddress, 1) BETWEEN 1 AND 255) 
)
GO

You can see different four part validation ,

First two part validation is : only IP Address range start with 12.4 Last two part validation is : 8 to 26 . 1 to 255.

Means valid IP Address are :

12.4.8.125

12.4.15.241

Invalid IP Address are:

10.2.21.52

12.4.7.124

For more details please visit: http://www.dbrnd.com/2015/06/validate-ip-address-range-in-sql-server/

How to restrict the length? Like 12345.789.22.7890 is invalid because the IP ranges from 0-255

Even if you break up the IP into it's respective octets, validating based solely on length of string will still produce false positives because any integer over 250 is three characters long.

The most validation that can be performed on an IP address without the subnet would be to use:

LIKE [1-250].[0-250].[0-250].[0-250]

...assuming submission as a single string (which I would consider using a CHECK constraint to enforce).

References:

• LIKE
• CHECK constraint