SQL Dynamically Split Values into new columns

user_13030290 0
Viewed 163

Have a table that contains different IP values for objects separated by commas. Looking for way to split them dynamically into new columns. For example,

10.12.11.20, 192.168.1.3, 192.168.1.4
192.168.1.50,

Based on the results returned in the table would like to split them into the respected columns (IPAddress0, IPAddress1, etc) Would it be easier/better to split these values into new rows?

Update:

Attempted the following:

 substring(ip,1,charindex(',',ip.IP)-1) as Col1
,substring(ip,charindex(',',ip.IP)+1,len(ip.IP)) as Col2

This resulted in 1st IP and 2nd IP into new columns, but if the object has more IPs than that they show up in Col2

charindex cross-apply sql-server sql
1 Answers

If you have SQL 2016 or later and rows are OK for you as well, you can use STRING_SPLIT as below.

SELECT VALUE FROM STRING_SPLIT ( '10.12.11.20,192.168.1.3,192.168.1.4' , ',' )
user_108640930
Related