# How to split the string and compute the value • ### 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

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

• 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()', '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()', '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

### 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
• 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 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()', '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()', '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
• Hello Hunchback,

Thank you very much! I had learned something new.

Cheers - DV

Tuesday, February 22, 2011 2:58 PM