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

Question

I have a varchar(max) field containing Name Value pairs, in every line I have Name UnderScore Value.

I need to do a query against it so that it returns the Name, Value pairs in two columns (so by parsing the text, removing the underscore and the "new line" char.

So from this

select NameValue from Table

where I get this text:

Name1_Value1
Name2_Value2
Name3_Value3

I would like to have this output

Names  Values
=====  ======
Name1  Value1
Name2  Value2
Name3  Value3

Answer 1

SELECT substring(NameValue, 1, charindex('_', NameValue)-1) AS Names, 
  substring(NameValue, charindex('_', NameValue)+1, LEN(NameValue)) AS Values
FROM Table

EDIT: Something like this put in a function or stored procedure combined with a temp table should work for more than one line, depending on the line delimiter you should also remove CHAR(13) before you 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) AS Names, 
      SUBSTRING(@current, CHARINDEX('_', @current)+1, LEN(@current)) AS Names
    SET @helper = SUBSTRING(@helper, CHARINDEX(CHAR(10), @helper)+1, LEN(@helper))
END
SELECT SUBSTRING(@helper, 1, CHARINDEX('_', @helper)-1) AS Names, 
  SUBSTRING(@helper, CHARINDEX('_', @helper)+1, LEN(@helper)) AS Names