0

Is there an easy way to get the sum of all decimal values in a string column.

Example:

"ABC $3.25, DEF $2.50, HIJ2 $0.25"
"ABC1 $3.25, DEF $2.50, HIJ $0.25, KLM $4.50"

I want to add only the values after the $ and before a ,.

Any help is highly appreciated.

1

If your SQL version is 2016 or later, you could try to use STRING_SPLIT function. Against earlier version, there are also lots of impletmentations of string split by searching GG.

Below is my example for SQL 2016+:

DECLARE @v nvarchar(256) = N'ABC $3.25, DEF $2.50, HIJ2 $0.25,ABC1 $3.25, DEF $2.50, HIJ $0.25, KLM $4.50'
SELECT  TRIM(value), RIGHT(value, LEN(value)-CHARINDEX('$',value))
FROM    STRING_SPLIT(@v,',')
SELECT  SUM(CONVERT(decimal(10,2),RIGHT(value, LEN(value)-CHARINDEX('$',value))))
FROM    STRING_SPLIT(@v,',')
1

The below example should work in SQL 2012. It assumes your string will always feature 3 alphabetical characters, a space, a dollar sign and a comma separating individual values. If this is not the case, you will either need to data cleanse or amend the script to handle the different scenarios.

Example:

DECLARE @Str VARCHAR(MAX) = 'ABC $3.25, DEF $2.50, HIJ $0.25, KLM $15.75, NOP $.75'

;WITH cte AS
(
    SELECT 0 a, 1 b
    UNION ALL
    SELECT b, CAST(CHARINDEX(',', @str, b) + LEN(',') AS INT)
    FROM CTE
    WHERE b > a
)

SELECT LEFT(value, PATINDEX('%[A-Za-z]%', value)+2) Code, REPLACE(value, LEFT(value, PATINDEX('%[A-Za-z]%', value)+2), '') AS Amount
FROM 
(
    SELECT REPLACE(REPLACE(SUBSTRING(@str, a,
    CASE 
        WHEN b > LEN(',') THEN b - a - LEN(',') 
        ELSE LEN(@str) - a + 1 
    END), ' ', ''), '$', '') AS value       
    FROM cte 
    WHERE a > 0
) src

Results:

Code    Amount
--------------
ABC     3.25
DEF     2.50
HIJ     0.25
KLM     15.75
NOP     .75
0

First you need Split String function ,DelimitedSplit2K

declare @t table(col1 varchar(50))
insert into @t VALUES
('ABC $3.25, DEF $2.50, HIJ2 $0.25')
,('ABC1 $3.25, DEF $2.50, HIJ $0.25, KLM $4.50');

With CTE1 as
 (
select col1,item
,stuff(item,1,PATINDEX('%[0-9]%',item)-1,'') aa
from @t
CROSS APPLY(select * from dbo.DelimitedSplit2K(col1,','))ca
)
,CTE AS(
select col1,item,SUBSTRING(item,CHARINDEX('$',item)+1,len(item))bb

from cte1
)
select col1,
sum(cast(bb as decimal(5,2)))  
from cte
--where ISNUMERIC(aa)=1
group by col1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.