I learned a new technique recently for shredding delimited strings.  It uses the xsd list type and SQL Server's xml data type and is quite powerful.  It's a bit different to most shredding techniques in that it allows easy shredding to columns (instead of rows).

USE tempdb
GO

CREATE XML SCHEMA COLLECTION testListSchema AS
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

 <xs:simpleType name="longintegerlist">
  <xs:list itemType="xs:long"/>
 </xs:simpleType>

 <xs:element name="Root">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="i" type="longintegerlist" />
   </xs:sequence>
  </xs:complexType>
 </xs:element>

</xs:schema>'
GO

DECLARE @t TABLE ( xCol XML(testListSchema) )

INSERT INTO @t VALUES ( '<Root>
<i>1 2 10 4</i>
</Root>')

SELECT 
 xCol.value('data(Root/i)[1]', 'INT'),
 xCol.value('data(Root/i)[2]', 'INT'),
 xCol.value('data(Root/i)[3]', 'INT'),
 xCol.value('data(Root/i)[4]', 'INT')
FROM @t
GO

-- Cleanup
IF EXISTS( SELECT * FROM sys.xml_schema_collections WHERE name = 'testListSchema' )
DROP XML SCHEMA COLLECTION testListSchema
GO

The technique does rely on the string being space delimited, but you could always use the SQL REPLACE function to clean the string before shredding.  The beauty of the technique is that it's actually quite simple, if you can get past the XSD list type.  So this is handy for shredding delimited strings when you know the number of elements you want to parse out and you want to parse to columns.

I hope you enjoyed our trip as we went beyond relational, parsing out delimited strings into columns using XML!