Solved

SQl Parsing

Posted on 2014-04-17
5
147 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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now