Solved

SQl Parsing

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 52
SQL Server - Set Field Values ito Zero Based on Related Table 4 44
SQL Server 2008 R2, need a pivot/cross tab query... 4 57
SQL Recursion 6 34
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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