SQL character delimited rows

Question

I have a SQL column with the following values:

Col1
22;34;56;70

I need to be able to create a query that will return 4 rows from that i.e:

Col1
22
34
56
70

How would I split by ;?

Answer 1

Try this:

create table #t(id varchar(max))
insert into #t values('22;34;56;70')

 SELECT   
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [id],  
         CAST ('<M>' + REPLACE([id], ';', '</M><M>') + '</M>' AS XML) AS String  
     FROM  #t) AS A CROSS APPLY String.nodes ('/M') AS Split(a);