October 10, 2006 at 4:59 am
I have a T-SQL statement that includes:
SELECT P.Col001, P.Col002, P.Col003, A.Age_start_INT, A.Age_end_INT, SUM(P.Col006)
But this is not allowed because P.Col006 is a string. Is it possible for me to Convert the text values to an int and do a SUM operation at the same time?
October 10, 2006 at 5:07 am
Yes. You can use both SUM(CAST(P.Col006 AS INT)) or SUM(CONVERT (INT, P.Col006)).
You can find this in the help files of SQL Server (BOL - Books OnLine) under "CAST and CONVERT".
October 10, 2006 at 5:19 am
a little better....
SUM((CASE WHEN ISNUMERIC(P.Col006) = 1 THEN CAST(P.Col006 AS INT) ELSE 0 END))
--Ramesh
October 10, 2006 at 6:03 am
ISNUMERIC does NOT check for numeric characters only... be careful.
--Jeff Moden
October 10, 2006 at 6:04 am
True, I didn't mention error checking... thanks for bringing it up, Ramesh.
Problem is that ISNUMERIC() does not always return what one would suppose. Be careful, even with this check some incorrect values may still pass the test - because they are considered numbers in some special form (like exponential):
SELECT ISNUMERIC('+002') --> 1
SELECT ISNUMERIC('-3E265') --> 1
SELECT ISNUMERIC('6D78') --> 1
SELECT ISNUMERIC('6D7897') --> 0
October 10, 2006 at 7:50 am
Vladan,
I does know that there are known issues with ISNUMERIC fn & i forgot to mention it..
Since 'D' & 'E' are valid formats for representing floating point numbers in Fortran & the internal routines used by SQL Server for ISNUMERIC uses the C functions that recognizes these formats. That doesn't necessarily mean that they can all be converted in T-SQL. MS is aware of these issues with this function & this has been reported to them several years back.
If you need better checks, then use the PATINDEX function to find matches. And also remember that ISNUMERIC returns TRUE for any conversion from string to any of the numeric data types not just int.
--Ramesh
October 10, 2006 at 6:26 pm
You can use the PatIndex if you want... I prefer the regular expressions because they seem to be simpler and they run with good speed...
For example, if you had a VARCHAR column called Col006 (from some of the previous examples that contained a mix of things that were summable (all digits) and things that were not summable and Col001 was the column to GORUP BY, the following would work nicely...
SELECT Col001,
Col006Sum = SUM(CASE WHEN Col006 NOT LIKE '%[^0-9]%'
THEN CAST(Col006 AS INT)
ELSE 0
END)
FROM yourtable
GROUP BY Col001
The "^" is the regular expression for NOT. Yeah, I know... two NOTS... trust me, it's still wicked fast and is guaranteed to correctly discriminate between ALL DIGITS or not where ISNUMERIC would allow errors to creep in.
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply