Create a column with IP addresses from the subnet column

Question

I've got two columns in the SQL database: SubnetID, SubnetName

Example:

 SubnetID  SubnetName

 1         1.2.3.0/24
 2         1.2.4.0/14
 3         1.2.5.4/30
 ...
 ...

Using SQL code, I need to add another column "IP Address" for each existing row (subnetID, SubnetName).

Output:

SubnetID  SubnetName   IP Address
 1         1.2.3.0/24   1.2.3.0
 1         1.2.3.0/24   1.2.3.1
 1         1.2.3.0/24   1.2.3.2
 ...       ...          ... and so on (till .24)
 1         1.2.3.0/24   1.2.3.24
 2         1.2.4.0/14   1.2.4.0  
 2         1.2.4.0/14   1.2.4.1 
 2         1.2.4.0/14   1.2.4.2
 ...       ...          ... and so on (till .14)
 2         1.2.4.0/14   1.2.4.14  
 3         1.2.5.4/30   1.2.5.4
 3         1.2.5.4/30   1.2.5.5
 3         1.2.5.4/30   1.2.5.6
 ...       ...          ... and so on (till .30)
 3         1.2.5.4/30   1.2.5.30
 ... etc.

So basically first two columns need to stay as is, but additional column will have individual addresses which cover the (.x/x) range.

Thank you very much for any suggestions.

Answer 1

If I understand your problem correctly, instead of adding a new column to your table, it is better to create another table looking like this:

SubnetID  IPAddress
-------------------    
   1       1.2.3.0
   1       1.2.3.1
   1       1.2.3.2
  ...        ...
   2       1.2.4.0
  ...        ...
   3       1.2.5.4

Your original tables stays just as it is:

SubnetID  SubnetName
---------------------
   1      1.2.3.0/24
   2      1.2.4.0/14
   3      1.2.5.4/30
  ...        ...

In my opinion, this is better than what you want to do, because now we don't duplicate all the SubnetName values and we can now avoid redundancies and the so-called update anomaly.

e.g. In your proposed example, what would happen if you wanted to change the SubnetName of id 2 from 1.2.4.0/14 to 1.2.4.0/15? You would have to change every row - but using this way, you only have to make the change once.

Answer 2

Try this solution,

with cte1 as (
    select *, RNUM = 
    row_number() over (partition by SubnetID order by SubnetID,SubnetName)-1 
     from demo_25_7_2018
     )
     select *,IPADDRESS=SUBSTRING(subnetname,1,charindex('/',subnetname,1)-1)+'.'+convert(varchar,rnum)
      from cte1

Sqlfiddle demo: http://sqlfiddle.com/#!18/0057d/1

let me know if this works fine.

Answer 3

In your question, I would suggest you do it on Business logic layer.

Thie is SQL server Version solution.

You can use CTE Recursion make it.

There are some main steps

1. write the first subquery use STRING_SPLIT function to split / get the IP and Number.
2. write the second subquery use STRING_SPLIT function to split . get the IP and MaxNumber and use CTE Recursion.
3. use REPLACE in main query to replace the IP Number.

Query

;WITH CTE AS (
    SELECT t1.SubnetID,
           t1.SubnetName,
           MAX(CASE WHEN rn = 1 THEN v.value END) startIP, 
           MAX(CASE WHEN rn = 2 THEN v.value END) MaxLevel
    FROM T t1
    CROSS APPLY (SELECT value,row_number() over (order by (select null)) rn FROM STRING_SPLIT(t1.SubnetName,'/')) v
    GROUP BY  t1.SubnetName,
              t1.SubnetID
),CTE1 AS(
   SELECT 
          t1.SubnetID,
          t1.SubnetName,
          t1.startIP,
          CAST(t1.MaxLevel AS INT) MaxLevel,
          CAST(MAX(CASE WHEN rn = 4 THEN v.value END)  AS INT) StartLevel,
          CAST(MAX(CASE WHEN rn = 4 THEN v.value END) AS INT) IPSub
   FROM CTE t1
   CROSS APPLY (SELECT value,row_number() over (order by (select null)) rn FROM STRING_SPLIT(t1.startIP,'.')) v
   GROUP BY  
          t1.SubnetName,
          t1.startIP,
          t1.MaxLevel,
          t1.SubnetID
   UNION ALL 
   SELECT 
          SubnetID,
          SubnetName,
          startIP,
          MaxLevel,
          (StartLevel + 1) StartLevel,
          IPSub
   FROM CTE1
   WHERE StartLevel < MaxLevel
)

SELECT 
  SubnetID,
  SubnetName,
  REPLACE(startIP,CONCAT('.',IPSub),CONCAT('.',StartLevel))
FROM CTE1
order by SubnetID

SQLfiddle

EDIT

If your SQL-server version didn't support STRING_SPLIT function. you can write your own splitstring function.

Here is a custom splitstring function from T-SQL split string

CREATE FUNCTION dbo.splitstring (@inputStr VARCHAR(MAX),@spitChar varchar(1))
RETURNS
 @returnList TABLE ([Value] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(@spitChar, @inputStr) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(@spitChar, @inputStr)  
  SELECT @name = SUBSTRING(@inputStr, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @inputStr = SUBSTRING(@inputStr, @pos+1, LEN(@inputStr)-@pos)
 END

 INSERT INTO @returnList
 SELECT @inputStr

 RETURN
END

Then you can use like sqlfiddle