Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQl Parsing

Posted on 2014-04-17
5
Medium Priority
?
155 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 36

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 36

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 36

Accepted Solution

by:
ste5an earned 1500 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Screencast - Getting to Know the Pipeline

926 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