Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQl Parsing

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Copy Database Wizard 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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

829 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