sql - How do I split a string so I can access item x? -

using sql server 2005, how split string can access item x?

for example

take string "hello john smith". how can split string space , access item @ index 1 should return "john"?

you may find solution in sql user defined function parse delimited string helpful (from the code project).

you can use simple logic:

declare @products varchar(200) = '1|20|3|343|44|6|8765' declare @individual varchar(20) = null  while len(@products) > 0 begin     if patindex('%|%', @products) > 0     begin         set @individual = substring(@products,                                     0,                                     patindex('%|%', @products))         select @individual          set @products = substring(@products,                                   len(@individual + '|') + 1,                                   len(@products))     end     else     begin         set @individual = @products         set @products = null         select @individual     end end