SELECT/extract the specific part of string BUT EVERY ROW HAS DIFFERENT LENTH

Question

Hei, I have a table LOG (Result - Picture) that shows how many meters a truck i warehouse treveled per day. I want to SUM meters per day after and before optimisation and export them as METERS BEFORE and METERS AFTER per DAY. When I excute my SQL it comes msg.: Conversation failed when converting the varchar '57.006' to data type int.

SELECT  s.datreg,
SUM (CONVERT(int, SUBSTRING (s.ma_desc,7,6),2)), 
SUM (CONVERT(int, SUBSTRING (s.ma_desc,39,7),2))
FROM S16T2 AS s
WHERE s.modul='MCAIDist'
GROUP BY s.datreg,s.ma_desc
ORDER BY datreg DESC

ORIGINAL SQL CODE :

SELECT S16T2.*  
FROM S16T2 
ORDER BY datreg DESC

The row on image shows "meters" before optimisation, and part after or. shows "meters" after optimisation.

Answer 1

You might have to convert it to decimal first. Why int?

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver16

Edit: I'm thinking your substring won't work because the numbers are in different lengths. You'll have to write a query that finds the colons instead of fixing the locations in your substring. Once you extract the number, I would still convert to decimal instead of int. Or, if there aren't that many variations in how the the colons are placed, build a function for each variation and call the function with a case statement based on what is in the field for each row.