2

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Sling4778
  • 21
  • 4
  • If you don't know the number of columns in the result set, this will be much more cumbersome than you probably expect. – Gordon Linoff Feb 10 '21 at 16:16
  • @GordonLinoff what would you propose on how to approach it if objects can contain one or more values? – Sling4778 Feb 10 '21 at 16:17
  • @Austin4778 What dbms are you using? Is it strictly required that you have a table with each ip as it's own column - what about a table with each ip as it's own row and you can wrangle it however you want when querying it. – Error_2646 Feb 10 '21 at 16:35
  • @Error_2646 SQL Server. new rows would be fine as well. Just need to split the addresses by the comma delimiter – Sling4778 Feb 10 '21 at 16:36
  • @Austin4778 This should do the trick for you then https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows – Error_2646 Feb 10 '21 at 16:41
  • Does this answer your question? [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) – Mark Schultheiss Feb 10 '21 at 19:33
  • @MarkSchultheiss that's the same article that Error_2646 provided – Sling4778 Feb 11 '21 at 15:22

1 Answers1

0

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' , ',' )
WAMLeslie
  • 1,241
  • 1
  • 5
  • 14