SQL Server – How to Sum a Varchar Data Type

sql serversql-server-2008-r2t-sql

In SQL Server 2008 I have a table with similar structure. Unfortunately changing the data structure is not an option, so I am stuck with attempting to come up with an alternative. I have tried to run this query, but I get an error of:

(5 row(s) affected)
Msg 8114, Level 16, State 5, Line 9
Error converting data type varchar to numeric.

Here is DDL – what syntax should be changed in the query in order for me to receive valid output and not an error message?

Declare @GreenHouse Table
(nomen varchar(100), vtc varchar(100), d1 date)

Insert Into @GreenHouse (nomen, vtc, d1) VALUES
('Green', '507.02', '2016-01-14'), ('Green', '4.44089e-015', '2016-01-03')
,('Green', '200.57', '2016-01-18'), ('Green', '649.01', '2016-01-19'),
('Green', '1849.85', '2016-04-30')

Select nomen, Round(Sum(Cast(vtc As Decimal(10,2))),0.00) As FormatedInfo, d1
FROM @GreenHouse Group By nomen, d1 Order By d1 DESC

Best Answer

The 4.44089e-015 is most likely giving you the error by trying to convert it to a decimal. Try using float instead:

SELECT  nomen, 
        ROUND(SUM(CAST(vtc AS float)),0.00) FormatedInfo, 
        d1
FROM @GreenHouse 
GROUP BY nomen, d1 
ORDER BY d1 DESC;

The results with your sample data are:

╔═══════╦══════════════╦════════════╗
║ nomen ║ FormatedInfo ║     d1     ║
╠═══════╬══════════════╬════════════╣
║ Green ║         1850 ║ 2016-04-30 ║
║ Green ║          649 ║ 2016-01-19 ║
║ Green ║          201 ║ 2016-01-18 ║
║ Green ║          507 ║ 2016-01-14 ║
║ Green ║            0 ║ 2016-01-03 ║
╚═══════╩══════════════╩════════════╝

(yeah, I know that it converts the 4.44089e-015 value to 0, but it is expected for a float)

Related Solutions

Sql-server – Why is there still a varchar data type

varchar work is good enough for a lot of Western European languages (Norwegian, Danish, German, French, Dutch etc too) subject to some collation issues
See this on SO varchar vs nvarchar performance nvarchar has serious performance implications
This is trivial compared to dealing with dates MDY vs DMY

SQL Server – Sorting Alphanumeric Values in VARCHAR Column

i am assuming before - you would have integer value always

declare @temp table
(val nvarchar(99))
insert into @temp values ('1-2'  )
insert into @temp values ('3-5'  )
insert into @temp values ('1-2A')
insert into @temp values ('6-12')
insert into @temp values ('6-7')
insert into @temp values ('6-12D')
insert into @temp values ('20-30')
insert into @temp values ('15-18')
--insert into @temp values ('3')
--insert into @temp values ('53')

QUERY

select * from @temp
order by CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)

Exactly Ordered

select * from @temp
order by CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)
,CAST(LEFT(RIGHT(val,LEN(val)-CHARINDEX('-',val)), patindex('%[^0-9]%', RIGHT(val,LEN(val)-CHARINDEX('-',val))+'.') - 1)  AS INT),
RIGHT(val,LEN(val)-CHARINDEX('-',val))

after run second query the output would be

UPDATE AFTER COMMENT: sometime varchar val won't come (3-5) or (1-12A) instead of whole number 3 or 5... at the time the above answer getting an error... how to correct way to sort.

-- DECLARE TABLE
DECLARE @temp TABLE
(val nvarchar(99))

--INSERT INTO TABLE
INSERT INTO @temp VALUES 
('1-2'), ('1'), ('3-5'), ('6'), ('1-2A'), ('6-12'), ('7-8'), ('9-12D'), ('9-12'), ('17'), ('9-12B'), ('19'), ('20-30'), ('15-18')

-- QUERY
SELECT * FROM @temp
ORDER BY
CASE 
    WHEN ISNUMERIC(val) = 0 THEN CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)
    WHEN ISNUMERIC(val) = 0 THEN CAST(LEFT(RIGHT(val,LEN(val)-CHARINDEX('-',val)), patindex('%[^0-9]%', RIGHT(val,LEN(val)-CHARINDEX('-',val))+'.') - 1)  AS INT)
    ELSE val 
    END ,val

RESULT:

Best Answer

Related Solutions

Sql-server – Why is there still a varchar data type

SQL Server – Sorting Alphanumeric Values in VARCHAR Column

Related Question