How to break a string value into a multiple columns in sql

how to break a string value into a multiple columns in sql

Depending on your requirements, you can use:

  • right function to grab text from the right
  • left function to grab text from the left
  • substring function to grab text from any position
  • charindex function to get position of a specific character
  • patindex function to get position of a specific pattern
  • reverse function in combination with charindex/patindex to get last position
  • rtrim function to exclude trailing spaces
  • ltrim function to exclude leading spaces
  • Jeff Moden's DelimitedSplit8K

Pedantic point: For splitting to Rows, rather than Columns

Take a look at this article hope this will help you out: http://social.technet.microsoft.com/wiki/contents/articles/26937.t-sql-splitting-a-string-into-multiple-columns.aspx

That function uses a WHILE Loop, which means the function will be slower to begin with and then further slowed by the fact that the function must be a Multi-statement TVF rather than an Inline TVF.