Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / NewStats: 3,194,602 members, 7,955,221 topics. Date: Saturday, 21 September 2024 at 07:49 PM |
Nairaland Forum / Science/Technology / Programming / How Do I Write Sql Statemen That Can Cast String To Double (9820 Views)
Programming Challenge: Convert String to Json with a Loop / [MOD] PHP WORKOUTS - String Manipulation 1 / How Do I Upload String To A Website In Vb.net And Get Response For Processing. (2) (3) (4)
How Do I Write Sql Statemen That Can Cast String To Double by luckyCO(m): 4:51am On Jun 21, 2007 |
I need SQL statement that can cast string (A column in a table) to double or int. I used this select Cast(Amount_Paid as int) from contractpayment but wasn't working. Amount paid is in string data type for a reason, Plz I need ur help |
Re: How Do I Write Sql Statemen That Can Cast String To Double by Fdeveloper(m): 10:08am On Jun 21, 2007 |
I assume you're using MS-SQL in which case you could try rounding the column first before the cast as maybe the column is stored in a decimal format select cast(round(amount_paid, 0) as int) as "AmountPaid" from contractpayment Alternatively, if your intention was to Cast as double then you should use the Double qualifier in the Cast() function [i.e. Cast(<Column Name> as Double)] I don't know if it's relevant but Oracle has a built function for converting varchar2 to a number as follows: to_number('1210.73', '9999.99') would return the number 1210.73 to_number('546', '999') would return the number 546 to_number('23', '99') would return the number 23 So your example would be written as follows: select to_number(amount_paid '9,999,999') as "AmountPaid" from contractpayment |
Re: How Do I Write Sql Statemen That Can Cast String To Double by luckyCO(m): 5:23am On Jun 22, 2007 |
Am using MySQL 5.1. I have used all these functions but none worked out for. I don't know what is the problem. Is still complain that I have not used the right MySQL syntax for cast. |
Re: How Do I Write Sql Statemen That Can Cast String To Double by Fdeveloper(m): 2:11pm On Jun 22, 2007 |
It could be that you are not using one of the correct type identifiers in your Cast() function. From what I can see in the documentation, "int" is not one of the defined types I got this from the MySQL Developer site http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html: CAST(expr AS type), CONVERT(expr,type), CONVERT(expr USING transcoding_name) I guess you should also ensure that the contents of the column can be safely converted in that it doesn't contain any non numeric characters such as currency indicators, semi colons etc. and also ensure that the column in question is not null. What is the output if you just do a straightforward select without a Cast() or Convert()? |
Re: How Do I Write Sql Statemen That Can Cast String To Double by luckyCO(m): 6:43pm On Jun 22, 2007 |
Thanks so much Fdeveloper I was casting to data types not supported. SO I used decimal and it worked. |
(1) (Reply)
Nigerian Programmers Can Be Of Great Help In The 2019 General Elections. / How To Change IP Address On Android Device / Are You A Real Programmer? Can You Code With Notepad?
(Go Up)
Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health religion celebs tv-movies music-radio literature webmasters programming techmarket Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 12 |