Solved

SQl Parsing

Posted on 2014-04-17
5
148 Views
Last Modified: 2014-04-23
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
Comment
Question by:Star79
  • 3
  • 2
5 Comments
 
LVL 33

Expert Comment

by:ste5an
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];

Open in new window

0
 

Author Comment

by:Star79
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 33

Expert Comment

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

Author Comment

by:Star79
ID: 40007219
sorry I mean to say integer 0 to n
0
 
LVL 33

Accepted Solution

by:
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;

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question