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

I have a va开发者_运维知识库rchar(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

---

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), @helper)-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

---

 DECLARE @TExt NVARCHAR(MAX)= '***[ddd]***
    dfdf
    fdfdfdfdfdf
    ***[fff]***
    4545445
    45454
    ***[ahaASSDAD]***

    DFDFDF
    ***[SOME   TEXT]***
    '

    DECLARE @Delimiter VARCHAR(1000)= CHAR(13) + CHAR(10) ;
    WITH    numbers
              AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o.object_id, o2.object_id ) Number
                   FROM     sys.objects o
                            CROSS JOIN sys.objects o2
                 ),
            c AS ( SELECT   Number CHARBegin ,
                            ROW_NUMBER() OVER ( ORDER BY number ) RN
                   FROM     numbers
                   WHERE    SUBSTRING(@text, Number, LEN(@Delimiter)) = @Delimiter
                 ),
            res
              AS ( SELECT   CHARBegin ,
                            CAST(LEFT(@text, charbegin) AS NVARCHAR(MAX)) Res ,
                            RN
                   FROM     c
                   WHERE    rn = 1
                   UNION ALL
                   SELECT   c.CHARBegin ,
                            CAST(SUBSTRING(@text, res.CHARBegin,
                                           c.CHARBegin - res.CHARBegin) AS NVARCHAR(MAX)) ,
                            c.RN
                   FROM     c
                            JOIN res ON c.RN = res.RN + 1
                 )
        SELECT  *
        FROM    res

---

He is an example that you can use:

-- Creating table:
create table demo (dID int, dRec varchar(100));

-- Inserting records:
insert into demo (dID, dRec) values (1, 'BCQP1 Sam');
insert into demo (dID, dRec) values (2, 'BCQP2 LD');

-- Selecting fields to retrive records:
select * from demo;

Then I want to show in one single row both rows combined and display only the values from the left removing the name on the right side up to the space character.

    /*
    The STUFF() function puts a string in another string, from an initial position.  
    The LEFT() function returns the left part of a character string with the specified number of characters. 
    The CHARINDEX() string function returns the starting position of the specified expression in a character string. 
*/
SELECT 
 DISTINCT
   STUFF((SELECT ' ' + LEFT(dt1.dRec, charindex(' ', dt1.dRec) - 1) 
          FROM demo dt1
          ORDER BY dRec
          FOR XML PATH('')), 1, 1, '') [Convined values]
FROM demo dt2
--
GROUP BY dt2.dID, dt2.dRec
ORDER BY 1

As you can see here when you run the function the output will be:

BCQP1 BCQP2

On the top of the script I explained what each function is used for (STUFF(), LEFT(), CHARINDEX() functions) I also used DISTINCT in order to eliminate duplicate values.

NOTE: dt stands for "demo table", I used the same table and use two alias dt1 and dt2, and dRec stands for "demo Record"

If you want to learn more about STUFF() Function here is a link:

https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/

---

With a CTE you will have a problem with Recursion if more that 100 items

Msg 530, Level 16, State 1, Line 20 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

DECLARE @TExt NVARCHAR(MAX)
SET @TExt = '100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203'


DECLARE @Delimiter VARCHAR(1000)= ',';
WITH    numbers
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o.object_id, o2.object_id ) Number
               FROM     sys.objects o
                        CROSS JOIN sys.objects o2
             ),
        c AS ( SELECT   Number CHARBegin ,
                        ROW_NUMBER() OVER ( ORDER BY number ) RN
               FROM     numbers
               WHERE    SUBSTRING(@text, Number, LEN(@Delimiter)) = @Delimiter
             ),
        res
          AS ( SELECT   CHARBegin ,
                        CAST(LEFT(@text, charbegin) AS NVARCHAR(MAX)) Res ,
                        RN
               FROM     c
               WHERE    rn = 1
               UNION ALL
               SELECT   c.CHARBegin ,
                        CAST(SUBSTRING(@text, res.CHARBegin,
                                       c.CHARBegin - res.CHARBegin) AS NVARCHAR(MAX)) ,
                        c.RN
               FROM     c
                        JOIN res ON c.RN = res.RN + 1
             )
    SELECT  *
    FROM    res