Splitting A String By A Delimiter, But Ignoring Multiple Delimiters

Hi,

I have a function which takes a string and a delimter. It then splits the string by the delimter and returns a table of resultant strings:


CREATE FUNCTION [dbo].[vs_SplitTags] (@sep char(1), @s varchar(512))

RETURNS table

AS

RETURN (

WITH Pieces(pn, start, stop) AS

(

SELECT 1, 1, CHARINDEX(@sep, @s)

UNION ALL

SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)

FROM Pieces

WHERE stop > 0

)

SELECT pn,

SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s

FROM Pieces

)




This works very well, other than instances of the delimter are, themselves, considered to be results. For example:

SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result
returns:
pn s
1 foo
2 bar


which is exactly the result I would want.

However,
SELECT * FROM vs_SplitTags(' ', ' foo bar ') AS result -- There are spaces before 'foo' and after 'bar'
returns
pn s
1
2 foo
3 bar
4

And
SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result -- There are two spaces between 'foo' and 'bar'
returns
pn s
1 foo
2
3
4 bar


I want the function to ignore whitespace altogether, be it a single space or multiple spaces. Other than to delimit the boundries between words, of course.

In other words, all three examples above should produce the same result:
pn s
1 foo
2 bar

How can I do this? Any thoughts much appreciated...