title: How Do I Split a Delimited String in SQL Server Without Creating a Function?
body: <p>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:</p>
  
  <p><a href="http://www.sommarskog.se/arrays-in-sql.html" rel="nofollow noreferrer">http://www.sommarskog.se/arrays-in-sql.html</a></p>
  
  <p>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.</p>
  
  <p>Without CREATE, I know I can use the PARSENAME function, something like this (Thanks to Nathan Bedford at <a href="https://stackoverflow.com/questions/2647">How do I split a string so I can access item x?</a>.):</p>
  
  <pre><code>SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2) 
  </code></pre>
  
  <p>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?</p>
  
  <p>EDIT:</p>
  
  <p>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.</p>