funnily enough, last night I came up with a function. I shall compare the two, but many thanks

GO

/****** Object: UserDefinedFunction [dbo].[split_test] Script Date: 28/06/2014 07:57:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[split_test]

(

@String NVARCHAR(4000),

@Delimiter NCHAR(1)

)

RETURNS TABLE

AS

RETURN

(

WITH Split(stpos,endpos)

AS(

SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos

UNION ALL

SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)

FROM Split

WHERE endpos > 0

)

---- SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),

---- 'Data' = ltrim(rtrim(SUBSTRING(@String,a.stpos,COALESCE(NULLIF(a.endpos,0),LEN(@String)+1)-a.stpos)))

---- FROM Split a

----CROSS apply split b

, basedata(id, [text])

AS ( SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),

'Data' = ltrim(rtrim(SUBSTRING(@String,a.stpos,COALESCE(NULLIF(a.endpos,0),LEN(@String)+1)-a.stpos)))

FROM Split a

CROSS apply split b),

cte(id, t, x)

AS (SELECT *,

CAST('<foo>' + REPLACE(REPLACE([text],'(','<bar>'),')','</bar>') + '</foo>' AS XML)

FROM basedata)

--SELECT * FROM basedata

SELECT DISTINCT a.text + ' '+ ISNULL(b.text,'') + ' ' + ISNULL(c.text,'') + ' ' + ISNULL(d.text,'') + ' ' + ISNULL(e.text,'') data FROM basedata a

LEFT outer JOIN basedata b ON a.text<>b.text --AND b.text<>c.text

LEFT outer JOIN basedata c ON a.text<>c.text AND c.text<>b.text

LEFT outer JOIN basedata d ON a.text<>d.text AND c.text<>d.text AND d.text<> b.text

LEFT outer JOIN basedata e ON a.text<>e.text AND b.text<>e.text AND d.text<> e.text AND c.text<> e.text

)