Solved

# SQl Parsing

Posted on 2014-04-17
146 Views
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
Question by:Star79
• 3
• 2

LVL 32

Expert Comment

ID: 40007156
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 Comment

ID: 40007180
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

LVL 32

Expert Comment

ID: 40007202
No, your definition says its "n|n|n|n".
0

Author Comment

ID: 40007219
sorry I mean to say integer 0 to n
0

LVL 32

Accepted Solution

ste5an earned 500 total points
ID: 40007256
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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…