SQL Server 2008 - split a multivalued column into rows with unique values

Question

In a SQL Server 2008 database, I have a multi-column column divided by half-colonies. Some values ​​contain colons. Sample data:

key:value;key2:value;blah;foo;bar;A sample value:whee;others key:value;blah;bar;others A sample value:whee 

I want to get all unique values ​​from each row in separate rows:

 key:value key2:value blah foo bar A sample value:whee others 

I looked at various split functions, but they all seem to be dealing with hard-coded rows, and not with rows from a column in a table. How can i do this?

Edit: Thomas received an answer! Here is my final request:

 With SampleInputs As ( select distinct myColumn from [myDatabase].[dbo].myTable where myColumn != '' ) , XmlCte As ( Select Cast( '<z>' + Replace( myColumn, ';', '</z><z>' ) + '</z>' As xml ) As XmlValue From SampleInputs As I ) Select Distinct Yzvalue('.','nvarchar(max)') As Value From XmlCte Cross Apply XmlValue.nodes('//z') Y(z) 

I assume that the material XmlValue.nodes and Yzvalue is magic. O_o

Answer 1

 With SampleInputs As ( Select 'key:value;key2:value;blah;foo;bar;A sample value:whee;others' As [Data] Union All Select 'key:value;blah;bar;others' Union All Select 'A sample value:whee' ) , XmlCte As ( Select Cast( '<z>' + Replace( I.[Data], ';', '</z><z>' ) + '</z>' As xml ) As XmlValue From SampleInputs As I ) Select Distinct Yzvalue('.','nvarchar(max)') As Value From XmlCte Cross Apply XmlValue.nodes('//z') Y(z) 

---

Update

Here is the version above that processes objects:

 With SampleInputs As ( Select 'key:value;key2:value;blah;foo;bar;A sample value:whee;others' As [Data] Union All Select 'key:value;blah;bar;others' Union All Select 'A sample value:whee' Union All Select 'A sample value:<Oops>' ) , XmlGoo As ( Select Cast( Replace( Replace( Cast( Z.XmlValue As nvarchar(max) ), '{{', '<z>' ) , '}}', '</z>') As Xml ) As Xmlvalue From ( Select Cast( ( Select '{{' + Replace( [Data], ';', '}}{{' ) + '}}' From SampleInputs For Xml Path(''), type ) As Xml ) As XmlValue ) As Z ) Select Distinct Z.data.value('.', 'nvarchar(max)') From XmlGoo Cross Apply XmlValue.nodes('/z') Z(data) 

Answer 2

With the split function you use, use cross:

 select distinct SS.part from YourTable cross apply dbo.SplitString(YourColumn, ';') as SS 

Here, SplitString takes two arguments, a row column and a separator, and has a column called part , where the values ​​are returned.