IP, MAC, and Network Subnet Address Validation In RDBMS

Network Addresses

This article is contributed equally by co-author Mr. Kartik Garghate.

We might need to validate an IPv4 address, IPv6 address, MAC address, or network subnet address from the backend using database scripts. In one assignment, we had to validate network addresses by writing a database script. We need to process a file by creating an Azure Data Factory pipeline and, while processing, validate the network addresses mentioned in the data file. If network addresses are in the correct format, save those records in the database; otherwise, discard the records having incorrect network addresses.

Network address validation can be quickly done using REGEXP in programming domains like API, UI, and Oracle database, but not in SQL Server. There is no ready-to-use implementation available to validate the network addresses in SQL Server; we needed to write a User Defined Function (UDF) to implement the solution. And for network address validation in Oracle, we need to modify the REGEXP.

In this article, we will see how to implement UDF in SQL Server and modify REGEXP in Oracle to validate IPv4, IPv6, MAC address, and Network Subnet. This article is written considering you have an intermediate level of expertise in database functions, IP validation concepts, and complex query writing.

Format of addresses

Let’s start by considering all the possible formats for addresses.

IPv4 Address

IP stands for Internet Protocol, and v4 stands for Version Four (IPv4). An IPv4 address has a format x.x.x.x where each x is called an octet, a decimal value between 0 and 255: for instance, 123.100.20.15 or 0.0.0.0. Octets are separated by periods, and an IPv4 address must contain three periods and four octets.

IPv6 Address

An IPv6 address is a 128-bit alphanumeric value that identifies an endpoint device in an IP Version 6 (IPv6) network. The preferred IPv6 address representation is x:x:x:x:x:x:x:x where each x is a 16-bit hexadecimal number. IPv6 addresses range from 0000:0000:0000:0000:0000:0000:0000:0000 to ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff.

In addition to this preferred format, IPv6 addresses may be specified in two other shortened formats:

• Omit leading zeros: We can specify IPv6 addresses by omitting leading zeros. For instance, the IPv6 address 1050:0000:0000:0000:0005:0600:300c:326b can be written as 1050:0:0:0:5:600:300c:326b.
• Double colon: We can specify IPv6 addresses by using double colons (::) in place of a series of zeros. Double colons are allowed only once in an IPv6 address. For example, the IPv6 address ff06:0:0:0:0:0:0:c3 can be written as ff06::c3.

MAC Address

A Media Access Control (MAC) address is the physical address that uniquely identifies each device on a given network. Traditional MAC addresses are 12-digit (6 bytes or 48 bits) hexadecimal numbers and may be written in the following ways:

• [00-FF]:[00-FF]:[00-FF]:[00-FF]:[00-FF]:[00-FF], as in 96:FF:FE:12:34:56
• [00-FF]-[00-FF]-[00-FF]-[00-FF]-[00-FF]-[00-FF], as in 96-FF-FE-12–34–56
• [0000-FFFF].[0000-FFFF].[0000-FFFF], as in 96FF.FE12.3456
• [000000000000-FFFFFFFFFFFF], as in 96FFFE123456

IPv4 Network Subnet

An IPv4 network subnet format combines IPv4 addresses and numbers from 0 to 32 separated by “/”. There are two formats for subnets:

• [IPv4]/[0–32], as in 10.10.10.10/11
• /[0–32], as in /10

Writing UDFs

Before writing our UDFs, let’s see why we need them.

• Modular Programming: You can create a function once, store it in the database, and call it any number of times in your program.
• Not Supported REGEXP: SQL Server does not support regular expressions like it is supported in other languages. The below IPv6 validation rule works in .NET and AngularJS but is not supported in SQL Server.

(?:^|(?<=\s))
 (
  ([0–9a-fA-F]{1,4}:){7,7}[0–9a-fA-F]{1,4}|
  ([0–9a-fA-F]{1,4}:){1,7}:|
  ([0–9a-fA-F]{1,4}:){1,6}:[0–9a-fA-F]{1,4}|
  ([0–9a-fA-F]{1,4}:){1,5}(:[0–9a-fA-F]{1,4}){1,2}|
  ([0–9a-fA-F]{1,4}:){1,4}(:[0–9a-fA-F]{1,4}){1,3}|
  ([0–9a-fA-F]{1,4}:){1,3}(:[0–9a-fA-F]{1,4}){1,4}|
  ([0–9a-fA-F]{1,4}:){1,2}(:[0–9a-fA-F]{1,4}){1,5}|
  [0–9a-fA-F]{1,4}:((:[0–9a-fA-F]{1,4}){1,6})|
  :((:[0–9a-fA-F]{1,4}){1,7}|:)|
  fe80:(:[0–9a-fA-F]{0,4}){0,4}%[0–9a-zA-Z]{1,}|
  ::(ffff(:0{1,4}){0,1}:){0,1}((25[0–5]|
  (2[0–4]|1{0,1}[0–9]){0,1}[0–9])\.){3,3}(25[0–5]|(2[0–4]|1{0,1}[0–9]){0,1}[0–9])|
  ([0–9a-fA-F]{1,4}:){1,4}:((25[0–5]|(2[0–4]|1{0,1}[0–9]){0,1}[0–9])\.){3,3}(25[0–5]|(2[0–4]|1{0,1}[0–9]){0,1}[0–9])
 )
(?=\s|$)

• Multiple scenarios to handle: As we mentioned, several addresses may be written in two or more equivalent formats.
• Cross Platform: We cannot call any method or function written in .NET or Java via a database program, but the programs written in .NET or Java can call and use functions defined in the database.

Let’s see UDFs to validate the network addresses one by one.

Validating IPv4 Addresses

In this section, let’s write functions to validate IPv4 addresses.

IPv4 Address Validation — SQL Server UDF

The below UDF validates the IPv4 address in SQL Server:

CREATE FUNCTION [dbo].[fn_ipv4_validation] 
(
   @p_ip_address NVARCHAR(300)
)
RETURNS BIT
AS
BEGIN
DECLARE @counter int = 0
/*
  RETURN 'FALSE' - Incorrect Format
  RETURN 'TRUE' - Correct Format
*/
-- Step 1
IF ( @p_ip_address LIKE '%[a-zA-Z]%' OR REPLACE(@p_ip_address,'.','') LIKE '%[^0-9]%' )
BEGIN
   SET @counter = 1
   RETURN 'FALSE'
END
-- Step 2
IF 
   @counter = 0  
   AND @p_ip_address = 
        ( SELECT 
            @p_ip_address 
          WHERE 
            (
              PARSENAME(@p_ip_address , 4) BETWEEN' 0' AND '255'
              AND PARSENAME(@p_ip_address , 3) BETWEEN '0' AND '255'
              AND PARSENAME(@p_ip_address , 2) BETWEEN '0' AND '255'
              AND PARSENAME(@p_ip_address , 1) BETWEEN '0' AND '255'
            )
        )
BEGIN
   RETURN 'TRUE'
END
-- Step 3
RETURN 'FALSE'
END
GO

UDF fn_ipv4_validation() is written to validate the IPv4 address format, which accepts an IPv4 address as a parameter @p_ip_address and returns ‘TRUE’ or ‘FALSE’. The steps followed in the UDF are explained below:

• Step 1: We will check if the string contains any characters from A-Z or a-z and any special characters apart from dot (.), if yes then it sets the counter variable value to 1, and it will return ‘FALSE’ and come out of the function. This check is done with the help of LIKE operator and REPLACE() function.
• Step 2: If the IPv4 address variable does not contain any characters from A-Z or a-z or any special characters other than a dot (.) and the counter variable is 0 then the 2nd step will execute. Here by using the PARSENAME() function we are validating if the parameter @p_ip_address is in [0–255].[0–255].[0–255].[0–255] format. If @p_ip_address is in the correct format, it will return ‘TRUE’ and will come out of the function.
• Step 3: If any of the preceding steps fail, the function returns ‘FALSE’ and exits.

The below example shows how to use fn_ipv4_validation()to validate IPv4 address:

SELECT 
    '12.210.60.67' IPV4_Value, 
    [dbo].[fn_ipv4_validation] ('12.210.60.67') IPv4_result
UNION ALL
  SELECT 
    '0.0.0.0' IPV4_Value, 
    [dbo].[fn_ipv4_validation] ('0.0.0.0') IPv4_result
UNION ALL
  SELECT 
    '10.10.10.asdasd' IPV4_Value, 
    [dbo].[fn_ipv4_validation] ('10.10.10.asdasd') IPv4_result
UNION ALL
  SELECT 
    CAST('10.10.as.10' as Varchar ) IPV4_Value, 
    [dbo].[fn_ipv4_validation] ('10.10.as.10') IPv4_result
UNION ALL
  SELECT 
    '0.0.0.0.0' IPV4_Value, 
    [dbo].[fn_ipv4_validation] ('0.0.0.0.0') IPv4_result
UNION ALL
  SELECT 
    '10.10.10.890' IPV4_Value, 
    [dbo].[fn_ipv4_validation] ('10.10.10.890') IPv4_result;

Following is the output from the above script:

IPv4 Address Validation — SQL Server Implementation

IPv4 Address Validation — Oracle

Below expression written using REGEXP_LIKE() helps to validate IPv4 address in Oracle:

REGEXP_LIKE(ipv4_address, '^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$')

The below example shows how to use the above expression to find out correct IPv4 addresses:

WITH 
  data
AS
(
    SELECT 
      '12.210.60.67' ipv4_address 
    FROM dual
  UNION ALL
    SELECT 
      '0.0.0.0' ipv4_address 
    FROM dual
  UNION ALL
    SELECT 
      '10.10.10.asdasd' ipv4_address 
    FROM dual
  UNION ALL
    SELECT 
      '10.10.as.10' ipv4_address 
    FROM dual
  UNION ALL
    SELECT 
      '0.0.0.0.0' ipv4_address 
    FROM dual
  UNION ALL
    SELECT 
      '10.10.10.890' ipv4_address 
    FROM dual
)
SELECT
  ipv4_address CORRECT_IPV4
FROM
  data
WHERE
  REGEXP(ip_address, '^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$');

The following image shows the output of the above script:

IPv4 Address Validation — Oracle Implementation

Validating IPv6 Addresses

In this section, let’s write functions to validate IPv6 addresses.

Character Count — SQL Server UDF

SQL Server does not have a built-in function for character count, which returns how many times a character is present in a given string. In IPv6 address validation UDF, we need to do some checks based on character count. So instead of writing the same logic, again and again, let’s create a UDF which accepts a string and a character and returns a character count:

CREATE FUNCTION [dbo].[fn_count_char] 
( 
   @p_input_string VARCHAR(1000), 
   @p_search_char CHAR(1) 
)
RETURNS INT
BEGIN

-- Step 1
DECLARE @v_input_string_length INT
DECLARE @v_index INT
DECLARE @v_count INT

SET @v_count = 0
SET @v_index = 1
SET @v_input_string_length = LEN(@p_input_string)

-- Step 2

WHILE @v_index <= @v_input_string_length
   BEGIN
      IF SUBSTRING(@p_input_string, @v_index, 1) = @p_search_char
         SET @v_count = @v_count + 1
         SET @v_index = @v_index + 1
   END

RETURN @v_count

END
GO

UDFfn_count_char()will show us how many times one particular string/character is present in the string passed. The steps followed in UDF are explained below:

• Step 1: Here we are declaring and initializing variables: @v_input_string_length to store the length of the string passed, @v_index to check the index of record and is set to 1, and @v_count to store the counter inside the loop.
• Step 2: We will run the while loop until the @v_index value is less than or equal to @v_input_string_length. In this loop, we will first check whether @p_search_char is present in the passed string @p_input_string using the substring function and if it matches the string to search then we will add one to @v_count and @v_index. As mentioned, this loop will continue till @v_index value is less than or equal to @v_input_string_length and at the end, we will return the total times the string/character is present in the string passed.

The below example shows how to use fn_count_char() to see how many times a given character appears in a string:

SELECT
  'Welcome Home' InputString, 
  [dbo].[fn_count_char]('Welcome Home', 'o') CharacterCount
UNION ALL
SELECT
  'Welcome Home' InputString, 
  [dbo].[fn_count_char]('Welcome Home', 'e') CharacterCount
UNION ALL
SELECT
  'Welcome Home' InputString, 
  [dbo].[fn_count_char]('Welcome Home', 'l') CharacterCount

The following image shows the output of the above script:

Character Count

IPv6 Address Validation — SQL Server UDF

The below UDF validates the IPv4 address format in SQL Server:

CREATE FUNCTION [dbo].[fn_ipv6_validation] 
(
   @p_ip_address NVARCHAR(300)
)
RETURNS BIT
AS
BEGIN

/*
  RETURN 'FALSE' - Incorrect Format
  RETURN 'TRUE' - Correct Format
*/

-- Step 1
DECLARE @v_updated_p_ip_address NVARCHAR(100)
DECLARE @v_char_count INT

-- Step 2
SET @v_char_count = dbo.fn_count_char(@p_ip_address, ':')

-- Step 3
IF ( @v_char_count > 7 OR @p_ip_address LIKE '%:::%')
   RETURN 'FALSE'

-- Step 4
IF
 (
   SELECT
     COUNT(1)
   FROM
     STRING_SPLIT(@p_ip_address, ':')
   WHERE
     LEN(VALUE)>4
 ) > 0
   RETURN 'FALSE'

-- Step 5
IF
 (
   (@p_ip_address like '%:' AND @p_ip_address NOT like '%::')
   OR (@p_ip_address like ':%' AND @p_ip_address NOT like '::%')
 )
   RETURN 'FALSE'

-- Step 6
IF CHARINDEX('::', @p_ip_address) > 0
   BEGIN
     IF @v_char_count BETWEEN 2 AND 6
         SET @p_ip_address = REPLACE(@p_ip_address, '::', REPLICATE(':', 7-(@v_char_count-2)))
   END

-- Step 7
SET @v_updated_p_ip_address = 
      (
        SELECT 
           STRING_AGG( RIGHT( REPLICATE( '0', 4) + value, 4), ':')
        FROM
           STRING_SPLIT( @p_ip_address,':')
      )

-- Step 8
IF @v_updated_p_ip_address LIKE STUFF( REPLICATE( ':[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]', 8), 1, 1, '')
   RETURN 'TRUE'

-- Step 9
RETURN 'FALSE'
END
GO

UDF fn_ipv6_validation() is written to validate the IPv6 address format, which accepts an IPv6 address as a parameter @p_ip_address and returns ‘TRUE’ or ‘FALSE’. The steps followed in UDF are explained below:

• Step 1: We will declare two variables, @v_updated_p_ip_address and @v_char_count. @v_char_count is used to save the count of character colon (:) in @p_ip_address and @v_updated_p_ip_address is used to store modified @p_ip_address.
• Step 2: In step 2 we calculate the count of colons (:) in the parameter @p_ip_address and assigned it to variable @v_char_count using the function fn_count_char().
• Step 3: Here we check if @v_char_count, calculated in the 2nd step, is greater than seven, or if @p_ip_address contains a group of more than two colons. If any of the conditions are satisfied, the function returns ‘FALSE’ and exits.
• Step 4: Split the @p_ip_address by a colon (:) using STRING_SPLIT() function. If a split value string contains more than four characters, then return ‘FALSE’ and exit the function.
• Step 5: IPv6 addresses should not contain a single or group of more than two colons at the start or end of the address. This part of the code checks for this condition and returns ‘FALSE’ if @p_ip_address passes this condition and exits the function.
• Step 6: If the IPv6 address is written in double colon format, at step 6 we will add the required colons, so there will be seven colons in the IPv6 address and will assign it to @p_ip_address. This we can implement by using the REPLICATE() and REPLACE() function as shown in the code.
• Step 7: Here we will ensure that numbers between colons are four digits long. If leading zeros are omitted in the IPv6 address parameter @p_ip_address, we will add those zeros by following the below steps,
  - Split the string based on the colon (:) by using the STRING_SPLIT() built-in function.
  - By using STRING_AGG(), RIGHT() and REPLICATE() built-in functions will make that IPv6 address in full format.
  E.g., if the value of the parameter @p_ip_address is 1050:0:0:0:5:600:300c:326b, then will update it to 1050:0000:0000:0000:0005:0600:300c:326b format and assign it to new variable @v_updated_p_ip_address.
• Step 8: We will check whether the IPv6 address passed is in the correct format, i.e., values are between 0–9 or a-f or A-F, as explained earlier. If this condition is satisfied, we will return ‘TRUE’ and exit.
• Step 9: If any of the preceding steps fail, the function returns ‘FALSE’ and exits.

The below example shows how to usefn_ipv6_validation()to validate IPv6 address:

SELECT
  '2001:db8:3333:4444:5555:6666:7777:8888:6543' Ipv6_value,
  [dbo].[fn_ipv6_validation] ('2001:db8:3333:4444:5555:6666:7777:8888:6543') IPV_Result
UNION ALL
SELECT 
  '2001:db8:3333:4444:5555:6666:7777:8888' Ipv6_value, 
  [dbo].[fn_ipv6_validation] ('2001:db8:3333:4444:5555:6666:7777:8888') IPV_Result
UNION ALL
SELECT 
  '2001:db8:5555::7777:8888' Ipv6_value,
  [dbo].[fn_ipv6_validation] ('2001:db:5555::7777:8888') IPV_Result
UNION ALL
SELECT 
  '::7777:8888' Ipv6_value, 
  [dbo].[fn_ipv6_validation] ('::7777:8888') IPV_Result
UNION ALL
SELECT 
  ':2001:db8:5555:6666:7777:8888::' Ipv6_value, 
  [dbo].[fn_ipv6_validation] (':2001:db8:5555:6666:7777:8888::') IPV_Result
UNION ALL
SELECT 
  '2001:db8:3333:4444:5555:6666:7777:8888:' Ipv6_value, 
  [dbo].[fn_ipv6_validation] ('2001:db8:3333:4444:5555:6666:7777:8888:') IPV_Result
UNION ALL
SELECT 
  ':2001:db8:3333:4444:5555:6666:7777:8888' Ipv6_value, 
  [dbo].[fn_ipv6_validation] (':2001:db8:3333:4444:5555:6666:7777:8888') IPV_Result;

The following image shows the output of the above script:

IPv6 Address Validation — SQL Server Implementation

IPv6 Address Validation — Oracle

Below expression written using REGEXP_LIKE() helps to validate IPv6 address in Oracle:

REGEXP_LIKE
 (ipv6_address, '(([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}|
               ([0-9a-fA-F]{1,4}:){1,7}:|
               ([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|
               ([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}|
               ([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}|
               ([0-9a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}|
               ([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}|
               [0-9a-fA-F]{1,4}:((:[0-9a-fA-F]{1,4}){1,6})|
               :((:[0-9a-fA-F]{1,4}){1,7}|:))'
 ) 

The below example shows how to use the above expression to validate IPv6 addresses:

WITH 
  data 
AS
(
    SELECT 
      '2001:db8:3333:4444:5555:6666:7777:8888:6543' ip_address 
    FROM 
      dual 
  UNION ALL
    SELECT 
      '2001:dc8:3333:4444:5555:6666:7777:8888' ip_address 
    FROM 
      dual 
  UNION ALL
    SELECT 
      '2001:d18:5555::7777:8888' ip_address 
    FROM 
      dual 
  UNION ALL
    SELECT 
      '::7777:8888' ip_address 
    FROM 
      dual 
  UNION ALL
    SELECT 
      ':2001:d18:5555:7777:8888::' ip_address 
    FROM 
      dual 
  UNION ALL
    SELECT 
      '2001:db8:3333:4444:5555:6666:7777:8888:' ip_address 
    FROM 
      dual 
  UNION ALL
    SELECT ':2001:db8:3333:4444:5555:6666:7777:8888' ip_address 
    FROM 
      dual
)
SELECT 
  ip_address
FROM 
  data
WHERE 
  REGEXP_LIKE
    (CASE 
        WHEN (ip_address LIKE '::%' AND ip_address NOT LIKE '%:') OR (ip_address LIKE '%::' AND ip_address NOT LIKE ':%')
          THEN ip_address
        WHEN ip_address LIKE ':%' OR ip_address LIKE '%:' 
          THEN '123'
        ELSE ip_address
      END, 
      '(([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,7}:|([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}|([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}|([0-9a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}|([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}|[0-9a-fA-F]{1,4}:((:[0-9a-fA-F]{1,4}){1,6})|:((:[0-9a-fA-F]{1,4}){1,7}|:))'
    )
AND ip_address NOT LIKE '%:::%'
AND REGEXP_COUNT(ip_address, ':') < 8
AND REGEXP_COUNT(ip_address, '::') < 2;

The following image shows the output of the above script:

IPv6 Address Validation — Oracle Implementation

Validating MAC Addresses

MAC Address Validation — SQL Server UDF

The below UDF validates the MAC address format in SQL Server:

CREATE FUNCTION [dbo].[fn_mac_address_validation]
(
  @p_mac_address NVARCHAR(300)
)
RETURNS BIT
AS
BEGIN

/*
  RETURN 'FALSE' - Incorrect Format
  RETURN 'TRUE' - Correct Format
*/

-- Step 1
IF
(
  -- Format: [00-FF]:[00-FF]:[00-FF]:[00-FF]:[00-FF]:[00-FF]
  @p_mac_address LIKE STUFF(REPLICATE(':[0-9a-fA-F][0-9a-fA-F]',6),1,1,'')
  -- Format: [00-FF]-[00-FF]-[00-FF]-[00-FF]-[00-FF]-[00-FF]
  OR @p_mac_address LIKE STUFF(REPLICATE('-[0-9a-fA-F][0-9a-fA-F]',6),1,1,'')
  -- Format: [0000-FFFF]-[0000-FFFF]-[0000-FFFF]
  OR @p_mac_address LIKE STUFF(REPLICATE('.[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]',3),1,1,'')
  -- Format: [000000000000-FFFFFFFFFFFF]
  OR @p_mac_address LIKE REPLICATE('[0-9a-fA-F]',12)
)
  RETURN 'TRUE'

-- Step 2
RETURN 'FALSE'
END
GO

fn_mac_address_validation() is a UDF written to validate the MAC address format, which accepts a MAC address as a parameter @p_mac_address and returns ‘TRUE’ or ‘FALSE’. The steps followed in UDF are explained below:

• Step 1: MAC addresses can be written in four different formats. So, in the 1st step, we will be verifying all formats, and if the parameter @p_mac_address passes any one of the formats, the function is returning ‘TRUE’.
• Step 2: If the previous step failed, the function returns ‘FALSE’ and exits

The below example shows how to use fn_mac_address_validation()to validate MAC addresses:

SELECT 
  '01-23-45-67-89-AB' Mac_Value, 
  [dbo].[fn_mac_address_validation]('01-23-45-67-89-AB') MAC_Result
UNION ALL
SELECT 
  '01:23:45:67:-89:AB' Mac_Value, 
  [dbo].[fn_mac_address_validation]('01:23:45:67:-89:AB') MAC_Result
UNION ALL
SELECT 
  '01:23:45:67:89:AB' Mac_Value, 
  [dbo].[fn_mac_address_validation]('01:23:45:67:89:AB') MAC_Result
UNION ALL
SELECT 
  '01.23.45.67.89.AH' Mac_Value, 
  [dbo].[fn_mac_address_validation]('01.23.45.67.89.AH') MAC_Result
UNION ALL
SELECT 
  '0123.4567.89Aa' Mac_Value, 
  [dbo].[fn_mac_address_validation]('0123.4567.89Aa') MAC_Result
UNION ALL
SELECT 
  '0123456789Aa' Mac_Value, 
  [dbo].[fn_mac_address_validation]('0123456789Aa') MAC_Result;

The following image shows the output of the above script:

MAC Address Validation — SQL Server Implementation

MAC Address Validation — Oracle

The below expressions using REGEXP_LIKE() help validate MAC address in Oracle:

-- For [00-FF]:[00-FF]:[00-FF]:[00-FF]:[00-FF]:[00-FF]
REGEXP_LIKE(mac_address, '^([0-9A-Fa-f]{2}[:]){5}([0-9A-Fa-f]{2})$')

-- For [00-FF]-[00-FF]-[00-FF]-[00-FF]-[00-FF]-[00-FF]
REGEXP_LIKE(mac_address, '^([0-9A-Fa-f]{2}[-]){5}([0-9A-Fa-f]{2})$')

-- For [0000-FFFF]-[0000-FFFF]-[0000-FFFF]
REGEXP_LIKE(mac_address, '^([0-9A-Fa-f]{4}[.]){2}([0-9A-Fa-f]{4})$')

-- For [000000000000-FFFFFFFFFFFF]
REGEXP_LIKE(mac_address, '^([0-9A-Fa-f]{12})$')

The below example shows how to use the above expression to find out correct MAC address:

WITH 
  data 
AS
(
    SELECT 
      '01-23-45-67-89-AB' mac_address 
    FROM 
      dual 
  UNION ALL
    SELECT 
      '01:23:45:67:-89:AB' mac_address 
    FROM 
      dual 
  UNION ALL
    SELECT 
      '01:23:45:67:89:AB' mac_address 
    FROM 
      dual 
  UNION ALL
    SELECT 
      '01.23.45.67.89.AH' mac_address 
    FROM 
      dual 
  UNION ALL
    SELECT 
      '0123.4567.89Aa' mac_address 
    FROM 
      dual 
  UNION ALL
    SELECT 
      '0123456789Aa' mac_address 
    FROM 
      dual
)
SELECT
  mac_address
FROM 
  data
WHERE
  (
    REGEXP_LIKE(mac_address, '^([0-9A-Fa-f]{2}[:]){5}([0-9A-Fa-f]{2})$')
    OR
    REGEXP_LIKE(mac_address, '^([0-9A-Fa-f]{2}[-]){5}([0-9A-Fa-f]{2})$')
    OR
    REGEXP_LIKE(mac_address, '^([0-9A-Fa-f]{4}[.]){2}([0-9A-Fa-f]{4})$')
    OR
    REGEXP_LIKE(mac_address, '^([0-9A-Fa-f]{12})$')
  );

The following image shows the output of the above script:

MAC Address Validation — Oracle Implementation

Validating IPv4 Subnet Addresses

In this section let’s write functions to validate IPv4 subnet addresses.

IPv4 Network Subnet Validation — SQL Server UDF

The below UDF validates the IPv4 Network Subnet format in SQL Server:

CREATE FUNCTION [dbo].[fn_network_subnet_validation]
(
  @p_network_subnet NVARCHAR(300)
)
RETURNS BIT
AS
BEGIN

/*
  RETURN 'FALSE' - Incorrect Format
  RETURN 'TRUE' - Correct Format
*/

-- Step 1
IF
  (
     SUBSTRING( @p_network_subnet, CHARINDEX( '/', @p_network_subnet) + 1, 10) LIKE '%[^0-9]%'
     OR LEN( SUBSTRING( @p_network_subnet, CHARINDEX( '/', @p_network_subnet) + 1, 10)) = 0
     OR LEN( SUBSTRING( @p_network_subnet, CHARINDEX( '/', @p_network_subnet) + 1, 10)) > 2
     OR SUBSTRING( @p_network_subnet, CHARINDEX( '/', @p_network_subnet) + 1, 10) LIKE '0_%'
     OR CHARINDEX( '/', @p_network_subnet) = 0
  )
    RETURN 'FALSE'

-- Step 2
IF
  (
     @p_network_subnet NOT LIKE '%[a-zA-Z]%'
     AND
       ( dbo.fn_ipv4_validation( SUBSTRING( @p_network_subnet, 1,
CASE WHEN CHARINDEX( '/', @p_network_subnet) = 0 THEN 
LEN(@p_network_subnet)
ELSE CHARINDEX( '/', @p_network_subnet) - 1
END )) = '1'                                             
         OR CHARINDEX( '/', @p_network_subnet) = '1'
       )
     AND 
       SUBSTRING( @p_network_subnet, CHARINDEX('/', @p_network_subnet) + 1, 10) BETWEEN '0' AND '32'
  )
    RETURN 'TRUE'
 
RETURN 'FALSE'
END
GO

fn_network_subnet_validation() UDF is written to validate the network subnet format, which accepts a network subnet as a parameter @p_network_subnet and returns ‘TRUE’ or ‘FALSE’. The steps followed in UDF are explained below:

• Step 1: If any of the below conditions satisfies for @p_network_subnet, function returns ‘FALSE’ and exits:
  - Checks if a substring after ‘/’ contains characters other than numbers.
  - Checks if the length of a substring after ‘/’ is zero.
  - Checks if the length of a substring after ‘/’ is greater than two.
  - Checks if a substring after ‘/’ starts with zero.
  - Checks if character ‘/’ is present in @p_network_subnet.
• Step 2: If all below conditions are passed, then the function returns ‘TRUE’ else returns ‘FALSE’ and exits:
  - @p_network_subnet does not contain any character from A-Z or a-z.
  - Subpart of a string @p_network_subnet before ‘/’ is a valid IPv4 address or 1st character of @p_network_subnet is ‘/’.
  - Substring after ‘/’ is between 0–32.

The below example shows how to use fn_network_subnet_validation()to validate an IPv4 network subnet address:

SELECT 
   '10.10.10.10/' Network_value, 
   [dbo].[fn_network_subnet_validation] ('10.10.10.10/') Network_Result 
UNION ALL 
SELECT 
  '10.10.10.10' Network_value, 
  [dbo].[fn_network_subnet_validation] ('10.10.10.10') Network_Result 
UNION ALL 
SELECT 
  '10.10.10.asdasd/11' Network_value, 
  [dbo].[fn_network_subnet_validation] ('10.10.10.asdasd/11') Network_Result 
UNION ALL 
SELECT 
  '10.10.10.10/31' Network_value,
  [dbo].[fn_network_subnet_validation] ('10.10.10.10/31') Network_Result 
UNION ALL 
SELECT 
  '10.10.10.10/ac' Network_value,
  [dbo].[fn_network_subnet_validation] ('10.10.10.10/ac') Network_Result 
UNION ALL 
SELECT 
  '/22' Network_value,
  [dbo].[fn_network_subnet_validation] ('/22') Network_Result 
UNION ALL 
SELECT 
  '/44' Network_value, 
  [dbo].[fn_network_subnet_validation] ('/44') Network_Result 
UNION ALL 
SELECT 
  '/' Network_value, 
  [dbo].[fn_network_subnet_validation] ('/') Network_Result 
UNION ALL 
SELECT 
  '/as' Network_value, 
  [dbo].[fn_network_subnet_validation] ('/as') Network_Result 
UNION ALL 
SELECT 
  '10.10.10.890/12' Network_value,
  [dbo].[fn_network_subnet_validation] ('10.10.10.890/12') Network_Result 
UNION ALL 
SELECT 
  '/1b' Network_value, 
  [dbo].[fn_network_subnet_validation] ('/1b') Network_Result 
UNION ALL 
SELECT 
  '/1.2' Network_value, 
  [dbo].[fn_network_subnet_validation] ('/1.2') Network_Result;

The following image shows the output of the above script:

IPv4 Network Address Validation — SQL Server Implementation

IPv4 Network Subnet Validation — Oracle

Below expression written using REGEXP_LIKE() validates IPv4 Network subnet address in Oracle:

REGEXP_LIKE(network_subnet, '^.{0}$|^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])(\/(3[0-2]|[1-2][0-9]|[0-9]))$|^(\/(3[0-2]|[1-2][0-9]|[0-9]))$')

The below example shows how to use the above expression to find out correct IPv4 network subnet address:

WITH 
  data 
AS
(
  SELECT 
    '10.10.10.10/' network_subnet 
  FROM 
    dual 
UNION ALL
  SELECT 
    '10.10.10.10' network_subnet 
  FROM 
    dual 
UNION ALL
  SELECT 
    '10.10.10.asdasd/11' network_subnet 
  FROM 
    dual 
UNION ALL
  SELECT 
    '10.10.10.10/31' network_subnet 
  FROM 
    dual 
UNION ALL
  SELECT 
    '10.10.10.10/ac' network_subnet 
  FROM 
    dual 
UNION ALL
  SELECT 
    '/22' network_subnet 
  FROM 
    dual 
UNION ALL
  SELECT 
    '/44' network_subnet 
  FROM 
    dual 
UNION ALL
  SELECT 
    '/' network_subnet 
  FROM 
    dual 
UNION ALL
  SELECT 
    '/as' network_subnet 
  FROM 
    dual 
UNION ALL
  SELECT 
    '10.10.10.890/12' network_subnet 
  FROM 
    dual 
UNION ALL
  SELECT 
    '/1b' network_subnet 
  FROM 
    dual 
UNION ALL
  SELECT 
    '/1.2' network_subnet 
  FROM 
    dual
)
SELECT
  network_subnet
FROM 
  data
WHERE 
  REGEXP_LIKE(network_subnet, '^.{0}$|^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])(\/(3[0-2]|[1-2][0-9]|[0-9]))$|^(\/(3[0-2]|[1-2][0-9]|[0-9]))$');

The following image shows the output of the above script:

IPv4 Network Address Validation — Oracle Implementation

Conclusion

This article provided you with the database scripts to validate network subnet addresses in SQL Server and Oracle, which you couldn’t easily do otherwise.