Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 157

# SQl Parsing

I have the following field in the database of type varchar
30|30|30|30|30|30|30|30|
How can I pull the first 30 out and then add up the rest like 30+30+30+30+30+30+30
The data keeps varying sometimes 4|4|4|4|4|4|4|4|4|4|..
its like any number
n|n|n|n
0
Star79
• 3
• 2
1 Solution

Senior DeveloperCommented:
Column, not field. E.g.

``````DECLARE @NumbersToStrip INT = 3;
DECLARE @WeirdSample TABLE ( Data NVARCHAR(MAX) );

INSERT INTO @WeirdSample
VALUES	( '30|30|30|30|30|30|30|30' ),
( '4|4|4|4|4|4|4|4|4|4' );

WITH Number AS (
SELECT	Data,
SUBSTRING( Data, 1, CHARINDEX('|', Data) - 1 ) AS Number
FROM	@WeirdSample
),
Occurances AS (
SELECT	Data,
Number,
LEN(REPLACE(Data, Number, '')) + 1 AS Occurances
FROM	Number
),
[Values] AS (
SELECT	Data,
Number,
Occurances,
Number * (Occurances - @NumbersToStrip) AS Value
FROM	Occurances
)
SELECT	*,
CASE WHEN [Value] < 0 THEN 0
ELSE [Value]
END AS FinalValue
FROM	[Values];
``````
0

Author Commented:
ste5an;
the sample can be like 60|50|35|35| as well the numbers need not be the same.Can you please modify the query
0

Senior DeveloperCommented:
No, your definition says its "n|n|n|n".
0

Author Commented:
sorry I mean to say integer 0 to n
0

Senior DeveloperCommented:
Then use a split function like Tally OH! An Improved SQL 8K “CSV Splitter” Function. E.g.

``````DECLARE @NumbersToStrip INT = 3;
DECLARE @WeirdSample TABLE ( Data NVARCHAR(MAX) );

INSERT INTO @WeirdSample
VALUES	( '30|30|30|30|30|30|30|30' ),
( '4|4|4|4|4|4|4|4|4|4' ),
( '60|50|35|35|17' );

SELECT	Data,
SUM(CAST(Item AS INT)) AS SumItems
FROM	@WeirdSample WS
CROSS APPLY dbo.DelimitedSplit8K(WS.Data, '|')
WHERE	ItemNumber > @NumbersToStrip
GROUP BY Data;
``````
0

## Featured Post

• 3
• 2
Tackle projects and never again get stuck behind a technical roadblock.