IF EXISTS (
	SELECT * from dbo.sysobjects
	WHERE 
		id = object_id(N'[dbo].[fn_Split]')
		AND xtype IN (N'FN', N'IF', N'TF')
	)
	DROP FUNCTION [dbo].[fn_Split]
GO


CREATE FUNCTION fn_Split (@text VARCHAR(MAX), @delimiter VARCHAR(20) = ' ')

	RETURNS @Strings TABLE (
		position INT IDENTITY PRIMARY KEY
		, value VARCHAR(MAX)
		)

AS BEGIN
	DECLARE @index int
	SET @index = -1

	WHILE (LEN(@text) > 0) BEGIN
		-- Find the first delimiter
		SET @index = CHARINDEX(@delimiter , @text)

		-- No delimiter left?
		-- Insert the remaining @text and break the loop
		IF (@index = 0) AND (LEN(@text) > 0) BEGIN  
			INSERT INTO @Strings VALUES (@text)
			BREAK 
		END 

		-- Found a delimiter
		-- Insert left of the delimiter and truncate the @text
		IF (@index > 1) BEGIN
			INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
			SET @text = RIGHT(@text, (LEN(@text) - @index))
		END
		-- Delimiter is 1st position = no @text to insert
		ELSE SET @text = RIGHT(@text, (LEN(@text) - @index))
	END
	RETURN
END
GO

DECLARE @foo TABLE (
	[Id] INT
	, [text] VARCHAR(255)
	)

INSERT @foo ([ID], [text])
      SELECT 1, 'asdas asdas asd rtwsdf fd'
UNION SELECT 2, 'sada asd asd a'

SELECT
	f.[ID]
	, f.[text]
	, f2.*
FROM
	@foo f

	CROSS APPLY dbo.fn_Split(f.[text], ' ')  f2

ID	text				position	value
1	asdas asdas asd rtwsdf fd	1		asdas
1	asdas asdas asd rtwsdf fd	2		asdas
1	asdas asdas asd rtwsdf fd	3		asd
1	asdas asdas asd rtwsdf fd	4		rtwsdf
1	asdas asdas asd rtwsdf fd	5		fd
2	sada asd asd a			1		sada
2	sada asd asd a			2		asd
2	sada asd asd a			3		asd
2	sada asd asd a			4		a

quote:

---

jsut use a string splitting function. There are lots of different way.

Peruse this for a more or less complete discussion:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

---