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

Cuellar

I close this question since I asked a new one that is closer to my real problem: #5830565 – Cuellar

No need to close. You can edit your questions – Solidago

Yes you are right, I was really in hurry! – Cuellar

The correct thing to do would be to design properly so you don't have more than one piece of information in a field. You should store this as two fields so you don't have to write nasty code to see the data properly every time you want to see it. Anytime you have a piece of data that you have to run functions on to see properly, you need to consider refactoring the design. One of the very first most basic rules of database design is that each field contains one and only piece of information. Better to parse once on data insert that to parse every time you select the data. – Readus

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

Solidago

So your DB design is borken? If you have more than one line you have to do this in a function or stored procedure. See my edit. – Solidago

no, what i need to do is a kind of temprary work for simualting new fields I don't have, of course i will remove this as soon as I have time. – Cuellar

Answer 2

 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

Typescript

Answer 3

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/

Koser

Answer 4

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

Pharos