[font="Courier New"]--================================================

-- Multi-statement Table-valued Function that splits an NVARCHAR(MAX)

-- into rows depending on a delimiter

--================================================

IF OBJECT_ID (N'splitStringToTable') IS NOT NULL

   DROP FUNCTION splitStringToTable

GO

CREATE FUNCTION splitStringToTable(@String NVARCHAR(MAX), @Delimiter VARCHAR(255))

RETURNS @strings TABLE

(

line_no INT IDENTITY(1,1),

theIndex INT DEFAULT 0,

previousIndex INT DEFAULT 0,

TheLine VARCHAR(255) DEFAULT '')

AS

-- body of the function

BEGIN

DECLARE @TheIndex INT, @Previousindex INT,@LenDelimiter INT

SELECT @TheIndex=-1, @LenDelimiter=LEN(@Delimiter)

--create the string table unfilled but the right length

INSERT INTO  @strings(TheIndex) SELECT 0 FROM numbers

WHERE number <=(LEN(@String)

       -LEN(REPLACE(@String,@Delimiter,'')))/LEN(@Delimiter)+1

--and populate the table

UPDATE  @strings

   SET @PreviousIndex=PreviousIndex=@TheIndex,

   @TheIndex=theIndex=CASE WHEN @PreviousIndex<>0

       THEN CHARINDEX(@Delimiter,@String,@PreviousIndex+1)ELSE 0 END,

   TheLine=CASE WHEN @TheIndex>0 THEN

       LEFT(SUBSTRING(@String,@PreviousIndex+@LenDelimiter,

               @TheIndex-@previousindex-@LenDelimiter),255)

   WHEN @PreviousIndex>0 THEN

       LEFT(SUBSTRING(@String,@PreviousIndex+@LenDelimiter,

               LEN(@String)-@previousindex),255)  

   ELSE '' END    

   RETURN

END

GO

DECLARE @mehitabelsExtensivePast VARCHAR(MAX)

SELECT @mehitabelsExtensivePast=

'

i have been

used something fierce in my time but

i am no bum sport archy

i am a free spirit archy i

look on myself as being

quite a romantic character oh the

queens i have been and the

swell feeds i have ate

a cockroach which you are

and a poet which you used to be

archy couldn t understand

my feelings at having come

down to this i have

had bids to elegant feeds where poets

and cockroaches would

neither one be mentioned without a

laugh archy i have had

adventures but i

have never been an adventuress'

SELECT * FROM dbo.splitStringToTable(@mehitabelsExtensivePast,'

')

[/font]