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

by

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)

More Related Contents:

  1. How do I generate a random number for each row in a T-SQL select?
  2. Auto increment primary key in SQL Server Management Studio 2012
  3. SQL Server Linked Server Example Query
  4. Combine multiple results in a subquery into a single comma-separated value
  5. Infinite loop CTE with OPTION (maxrecursion 0)
  6. SQL Server IN vs. EXISTS Performance
  7. Floor a date in SQL server
  8. How to drop a table if it exists?
  9. PIVOT in sql 2005
  10. How to run the same query on all the databases on an instance?
  11. Atomic UPSERT in SQL Server 2005
  12. How FOR XML PATH(”) works when concatenating rows
  13. SQL Server ORDER BY date and nulls last
  14. SSRS 2014 report deploy issue
  15. Set start value for column with autoincrement
  16. SqlDataAdapter.Fill method slow
  17. How to update primary key
  18. How to determine the field value which can not convert to (decimal, float,int) in SQL Server
  19. Making an Entity Framework Model span multiple databases
  20. SQL Server 2008 Row Insert and Update timestamps
  21. Full text search does not work if stop word is included even though stop word list is empty
  22. Should I index a bit field in SQL Server?
  23. Trouble Connecting to sql server Login failed. “The login is from an untrusted domain and cannot be used with Windows authentication”
  24. In SQL Server, how can I lock a single row in a way similar to Oracle’s “SELECT FOR UPDATE WAIT”?
  25. Escaping single quote in SQL Server
  26. SQL Server 2008 can’t login with newly created user
  27. Update SQL with consecutive numbering
  28. Nested transactions in Sql Server
  29. What is the difference between Cartesian product and cross join?
  30. Count number of records returned by group by

Leave a Comment