How Do I Split a Delimited String in SQL Server Without Creating a Function?

Question

I'm working with a SQL Server database. I have a column which contains a delimited list, and I need to write a query which splits the values of the list into rows. From browsing StackOverflow and the rest of the web, I know this is a common problem. In fact, I found an extensive analysis here:

http://www.sommarskog.se/arrays-in-sql.html

Unfortunately, every solution I've seen on that site and elsewhere requires me to create a function. That isn't an option for me -- I lack the privileges required to use the CREATE command.

Without CREATE, I know I can use the PARSENAME function, something like this (Thanks to Nathan Bedford at How do I split a string so I can access item x?.):

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2) 

However, PARSENAME works only for lists of 4 items or fewer. My question, therefore, is this: How do I write a query to split a delimited string of more than 4 items without creating new objects in the database?

EDIT:

Thanks to everyone for the quick answers. I may have left out some important information -- I'm interacting with the database through an ODBC connection. In addition to CREATE statements, there seem to be other statements that don't work. For instance, I can't seem to use DECLARE in one statement to define a variable that will be used in another statement. As near as I can figure, I have to put everything into a single SELECT statement (although WITH also seems to work for declaring common tables). Unfortunately, all of the solutions suggested so far seem to require variable declarations outside the SELECT statement, and that isn't working. Please bear with me -- I'm learning as I go.

Answer 1

A version using XML.

declare @S varchar(100) = 'Hello John Smith'

select 
  n.r.value('.', 'varchar(50)')
from (select cast('<r>'+replace(@S, ' ', '</r><r>')+'</r>' as xml)) as s(XMLCol)
  cross apply s.XMLCol.nodes('r') as n(r)

Using a table instead Replace @T with what ever table you are using.

-- Test table
declare @T table (ID int, Col varchar(100))
insert into @T values (1, 'Hello John Smith')
insert into @T values (2, 'xxx yyy zzz')

select 
  T.ID,
  n.r.value('.', 'varchar(50)')
from @T as T
  cross apply (select cast('<r>'+replace(replace(Col,'&','&amp;'), ' ', '</r><r>')+'</r>' as xml)) as S(XMLCol)
  cross apply S.XMLCol.nodes('r') as n(r)

Splitting the string 'Hello John Smith' without using a variable

select 
  n.r.value('.', 'varchar(50)')
from (select cast('<r>'+replace('Hello John Smith', ' ', '</r><r>')+'</r>' as xml)) as s(XMLCol)
  cross apply s.XMLCol.nodes('r') as n(r)

Answer 2

example using the built in master..spt_values table

DECLARE @String VARCHAR(1000)
    SELECT @String ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5'

    SELECT SUBSTRING(',' + @String + ',', Number + 1,
    CHARINDEX(',', ',' + @String + ',', Number + 1) - Number -1)AS VALUE
    FROM master..spt_values
    WHERE Type = 'P'
    AND Number <= LEN(',' + @String + ',') - 1
    AND SUBSTRING(',' + @String + ',', Number, 1) = ','
    GO

See here for more: Split A String By Using A Number Table