sql server - How to parse a string and create several columns from it? -

i have varchar(max) field containing name value pairs, in every line have name underscore value.

i need query against returns name, value pairs in 2 columns (so parsing text, removing underscore , "new line" char.

so this

select namevalue table 

where text:

name1_value1 name2_value2 name3_value3 

i have output

names  values =====  ====== name1  value1 name2  value2 name3  value3 

select substring(namevalue, 1, charindex('_', namevalue)-1) names,    substring(namevalue, charindex('_', namevalue)+1, len(namevalue)) values table 

edit: put in function or stored procedure combined temp table should work more 1 line, depending on line delimiter should remove char(13) before start:

declare @helper varchar(512) declare @current varchar(512) set @helper = namevalue while charindex(char(10), @helper) > 0 begin     set @current = substring(@helper, 1, charindex(char(10), namevalue)-1)     select substring(@current, 1, charindex('_', @current)-1) names,        substring(@current, charindex('_', @current)+1, len(@current)) names     set @helper = substring(@helper, charindex(char(10), @helper)+1, len(@helper)) end select substring(@helper, 1, charindex('_', @helper)-1) names,    substring(@helper, charindex('_', @helper)+1, len(@helper)) names