none
How to split the string and compute the value RRS feed

  • Question

  •  

    hi i want to compute this coup_comb column value

     

    id    coup_comb

     1     A,11 ,0;B,2 ,20

     2    A,2 ,5;B,11 ,0;C,1 ,5

     

    getting the values bold in the above data next to the A,B,C and replace the following value based on condition

    contd:

    if 11 then   return  N/A

    if 2    then  return  <!-- [if gte mso 10]> <mce:style> $ XX OFF

    if 1   then  return  <!-- [if gte mso 10]> <mce:style> $


    i want the o/p as below format

    id    coup_comb

     1     A,N/A ,0;B,$ XX OFF ,20

     2     A,$ XX OFF ,5;B,N/A ,0;C,$ ,5

     

    how to do this operation please help me i'm stuck in this operation.

     


    Sudhesh. G
    http://gurucoders.blogspot.com
    Tuesday, February 22, 2011 11:50 AM

Answers

  • What is this, a double delimited list stored in a varchar column?

    I think it will be eaiser if you register a SQLCLR function that allows you to use regular expressions to do the replace. You can also try using an xml doc with a more detailed schema, or normalizing the model.

    Here is a possible solution, but as you will notice it is combersome and complex.

    USE tempdb;
    GO
    DECLARE @T TABLE (
    id int NOT NULL PRIMARY KEY,
    coup_comb varchar(MAX) NOT NULL
    );
    
    INSERT INTO @T VALUES(1, 'A,11,0;B,2,20');
    INSERT INTO @T VALUES(2, 'A,2,5;B,11,0;C,1,5');
    
    WITH rs1 AS (
    SELECT
    	T.id, T.coup_comb, 
    	T5.s,
    	DENSE_RANK() OVER(PARTITION BY T.id ORDER BY N1.e) AS rn1, 
    	ROW_NUMBER() OVER(PARTITION BY T.id, T3.s ORDER BY N2.e) AS rn2
    FROM
    	@T AS T
    	CROSS APPLY
    	(SELECT T.coup_comb AS [*] FOR XML PATH('')) AS T1(s)
    	CROSS APPLY
    	(SELECT CAST('<e>' + REPLACE(T1.s, ';', '</e><e>') + '</e>' AS xml)) T2(x)
    	CROSS APPLY
    	T2.x.nodes('e') AS N1(e)
    	CROSS APPLY
    	(SELECT N1.e.value('text()[1]', 'varchar(MAX)')) AS T3(s)
    	CROSS APPLY
    	(SELECT CAST('<e>' + REPLACE(T3.s, ',', '</e><e>') + '</e>' AS xml)) T4(x)
    	CROSS APPLY
    	T4.x.nodes('e') AS N2(e)
    	CROSS APPLY
    	(SELECT N2.e.value('text()[1]', 'varchar(MAX)')) AS T5(s)
    ),
    rs2 AS (
    SELECT
    	id, coup_comb,	
    	CASE
    	WHEN rn2 = 2 AND RTRIM(LTRIM(s)) = '11' THEN 'N/A' 
    	WHEN rn2 = 2 AND RTRIM(LTRIM(s)) = '2' THEN '$ XX OFF'
    	WHEN rn2 = 2 AND RTRIM(LTRIM(s)) = '1' THEN '$'
    	ELSE s
    	END AS se
    	,
    	rn1,
    	rn2
    FROM
    	rs1
    ),
    rs3 AS (
    SELECT
    	id, rn1,
    	STUFF(
    	(
    	SELECT
    		',' + se
    	FROM
    		rs2 AS B
    	WHERE
    		B.id = A.id AND B.rn1 = A.rn1
    	ORDER BY
    		B.rn2
    	FOR XML PATH('')
    	), 1, 1, '') AS e
    FROM
    	rs2 AS A
    GROUP BY
    	id, rn1
    )
    SELECT
    	id,
    	STUFF(
    	(
    	SELECT
    		';' + e
    	FROM
    		rs3 AS B
    	WHERE
    		B.id = T.id
    	ORDER BY
    		B.rn1
    	FOR XML PATH('')
    	), 1, 1, '') AS coup_comb
    FROM
    	@T AS T
    ORDER BY
    	id;
    GO
    
    /*
    
    id	coup_comb
    1	A,N/A,0;B,$ XX OFF,20
    2	A,$ XX OFF,5;B,N/A,0;C,$,5
    
    */
    

    The idea is to split each list, the first one delimited by semicolon, and the second delimited by comma. Find the second element in each list and use the CASE function to return the proper value. Then we need to pull back the elements to construct the lists.

     

     


    AMB

    Some guidelines for posting questions...

    Tuesday, February 22, 2011 2:48 PM
    Moderator

All replies

  • Take a look at REPLACE function

    SELECT REPLACE(REPLACE('A,11 ,0;B,2 ,20','11','N/A'),'2','$ XX OFF')


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, February 22, 2011 1:35 PM
    Answerer
  • Sudhesh & Uri – I am new to T-SQL and I had tried something… I am sure this is not the efficient way to solve this problem. I am trying to learn by solving the issues.

     

    SELECT ID, (STUFF((STUFF(STUFF(Coup_Comb, CHARINDEX('2',COUP_COMB,1), LEN('2'), '$ XX OFF'),

                                                    (CHARINDEX('11',STUFF(Coup_Comb, CHARINDEX('2',COUP_COMB,1), LEN('2'), '$ XX OFF'),1)),

                                                    LEN('11'),

                                                    'N/A')),

                                                                (CHARINDEX('1',(STUFF(STUFF(Coup_Comb, CHARINDEX('2',COUP_COMB,1), LEN('2'), '$ XX OFF'),

                                                                (CHARINDEX('11',STUFF(Coup_Comb, CHARINDEX('2',COUP_COMB,1), LEN('2'), '$ XX OFF'),1)),

                                                                LEN('11'),

                                                                'N/A')),1)),

                                                    LEN('1'),

                                                    '$')) as New_Coup_Comb

    FROM Testdb.dbo.Test2

    WHERE ID = 2

     

    Cheers - DV

    • Proposed as answer by DVadithala Tuesday, February 22, 2011 2:19 PM
    Tuesday, February 22, 2011 2:17 PM
  • What is this, a double delimited list stored in a varchar column?

    I think it will be eaiser if you register a SQLCLR function that allows you to use regular expressions to do the replace. You can also try using an xml doc with a more detailed schema, or normalizing the model.

    Here is a possible solution, but as you will notice it is combersome and complex.

    USE tempdb;
    GO
    DECLARE @T TABLE (
    id int NOT NULL PRIMARY KEY,
    coup_comb varchar(MAX) NOT NULL
    );
    
    INSERT INTO @T VALUES(1, 'A,11,0;B,2,20');
    INSERT INTO @T VALUES(2, 'A,2,5;B,11,0;C,1,5');
    
    WITH rs1 AS (
    SELECT
    	T.id, T.coup_comb, 
    	T5.s,
    	DENSE_RANK() OVER(PARTITION BY T.id ORDER BY N1.e) AS rn1, 
    	ROW_NUMBER() OVER(PARTITION BY T.id, T3.s ORDER BY N2.e) AS rn2
    FROM
    	@T AS T
    	CROSS APPLY
    	(SELECT T.coup_comb AS [*] FOR XML PATH('')) AS T1(s)
    	CROSS APPLY
    	(SELECT CAST('<e>' + REPLACE(T1.s, ';', '</e><e>') + '</e>' AS xml)) T2(x)
    	CROSS APPLY
    	T2.x.nodes('e') AS N1(e)
    	CROSS APPLY
    	(SELECT N1.e.value('text()[1]', 'varchar(MAX)')) AS T3(s)
    	CROSS APPLY
    	(SELECT CAST('<e>' + REPLACE(T3.s, ',', '</e><e>') + '</e>' AS xml)) T4(x)
    	CROSS APPLY
    	T4.x.nodes('e') AS N2(e)
    	CROSS APPLY
    	(SELECT N2.e.value('text()[1]', 'varchar(MAX)')) AS T5(s)
    ),
    rs2 AS (
    SELECT
    	id, coup_comb,	
    	CASE
    	WHEN rn2 = 2 AND RTRIM(LTRIM(s)) = '11' THEN 'N/A' 
    	WHEN rn2 = 2 AND RTRIM(LTRIM(s)) = '2' THEN '$ XX OFF'
    	WHEN rn2 = 2 AND RTRIM(LTRIM(s)) = '1' THEN '$'
    	ELSE s
    	END AS se
    	,
    	rn1,
    	rn2
    FROM
    	rs1
    ),
    rs3 AS (
    SELECT
    	id, rn1,
    	STUFF(
    	(
    	SELECT
    		',' + se
    	FROM
    		rs2 AS B
    	WHERE
    		B.id = A.id AND B.rn1 = A.rn1
    	ORDER BY
    		B.rn2
    	FOR XML PATH('')
    	), 1, 1, '') AS e
    FROM
    	rs2 AS A
    GROUP BY
    	id, rn1
    )
    SELECT
    	id,
    	STUFF(
    	(
    	SELECT
    		';' + e
    	FROM
    		rs3 AS B
    	WHERE
    		B.id = T.id
    	ORDER BY
    		B.rn1
    	FOR XML PATH('')
    	), 1, 1, '') AS coup_comb
    FROM
    	@T AS T
    ORDER BY
    	id;
    GO
    
    /*
    
    id	coup_comb
    1	A,N/A,0;B,$ XX OFF,20
    2	A,$ XX OFF,5;B,N/A,0;C,$,5
    
    */
    

    The idea is to split each list, the first one delimited by semicolon, and the second delimited by comma. Find the second element in each list and use the CASE function to return the proper value. Then we need to pull back the elements to construct the lists.

     

     


    AMB

    Some guidelines for posting questions...

    Tuesday, February 22, 2011 2:48 PM
    Moderator
  • Hello Hunchback,

    Thank you very much! I had learned something new.

     

    Cheers - DV

    Tuesday, February 22, 2011 2:58 PM