Any SQL Experts?

[Brentastic]

Ok, so I'm trying to get familiar with microsoft SQL. I'm having trouble converting a character (in this case principal balance) to an integer so I can sum. Don't ask me why this data is stored as a character (our IT dept created the table), but regardless, I need to have it as an integer for summing etc...

[10g_DBA]

Ok, so I'm trying to get familiar with microsoft SQL. I'm having trouble converting a character (in this case principal balance) to an integer so I can sum. Don't ask me why this data is stored as a character (our IT dept created the table), but regardless, I need to have it as an integer for summing etc...

This might get you started.

http://dba.fyicenter.com/faq/sql_server/CO...ric_Values.html

 

I'd consider using the float data type if you need cents. Integer may just work for whole dollars.

 

If this were Oracle, I could help more. Good luck.

[ts]

One of these commands might do what you are looking for:

 

TO_NUMBER — A string function that converts a given string expression to a value of NUMBER data type.

CONVERT — A function that converts a given expression to a specified data type.

 

-----

 

TO_NUMBER(string-expression)

 

TONUMBER(string-expression)

 

[string-expression] The string expression to be converted. The expression can be the name of a column, a string literal, or the result of another function, where the underlying data type is of type CHAR or VARCHAR2.

-----

 

{fn CONVERT(expression,datatype)}

 

CONVERT(datatype,expression,format-code)

 

[expression] The expression to be converted.

[datatype] The data type to which expression is to be converted.

[format-code Optional] — An integer code that specifies date and time formats, used to convert between date/time/datestamp data types and character data types. This argument is only used with the second syntax form.

[Brentastic]

I searched all over google for 'convert char to integer' and never found anything that worked. Finally asked our 'internet guy' at work (hate resorting to that). Basically, it's a 'cast' command. Something like this:

 

Select CAST ([dbo.tablename], AS NUMERIC (13,2), AS Total Balance

From dbo.tablename

The 13 defines the max total digits assigned to the number, the 2 defines the number of decimall places. Anyways, my first day trying out SQL after being on Access and Excel only. I think I will like SQL most once I get the hang of it and all it's capabilities. Unfortunately, IT seems to hold back on granting us analysts full access (right now we can't create views or functions) - I think it makes them more expendable.