Db2 Sql Cast Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site db2 sql round About Us Learn more about Stack Overflow the company Business Learn more about
Db2 Sql Replace
hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join
Db2 Sql If
the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Varchar to Decimal conversion http://www.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023459.html in db2 up vote 1 down vote favorite I am trying to convert a varchar field to decimal field in db2 but I am getting an error AMOUNT field is a varchar field which needs to be converted to decimal(31,3) Given below is my query SELECT CAST(ROUND(TRIM(AMOUNT),3) AS DECIMAL(31,3)) FROM TABLENAME Note: AMOUNT field (varchar) has a NULL value as well Sample values: 7.324088 -42.97209 854 http://stackoverflow.com/questions/23227240/varchar-to-decimal-conversion-in-db2 NULL 6 6 350 -6 15.380584 1900 I get the below error: Invalid character found in a character string argument of the function "DECFLOAT". SQLSTATE=22018 sql casting db2 share|improve this question edited Apr 26 at 13:23 giliev 7971725 asked Apr 22 '14 at 18:15 Rikin 861413 The fact that it's saying DECFLOAT (as opposed to DECIMAL) indicates that ROUND(...) is what's balking. This is a floating-point number (has a sliding range), although because it's decimal-based it can actually store things like .1 - is this okay? Try to find out what type of value is actually throwing this error. Do you have alpha characters at all in this column? One way to try this is to do an ordered INSERT into some other table (without transactions/ROLLBACK), then lookup the value after the last inserted. –Clockwork-Muse Apr 23 '14 at 9:07 add a comment| 2 Answers 2 active oldest votes up vote 3 down vote Try doing like SELECT CAST(ROUND(COALESCE(TRIM(AMOUNT),0),3) AS DECIMAL(31,3)) FROM TABLENAME share|improve this answer answered Apr 22 '14 at 18:52 Rahul 43.4k63462 Rahul, thank you for your response. But I still get the same error. As I mentio
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us http://stackoverflow.com/questions/33518419/avoid-having-error-when-converting-string-to-integer-in-db2 Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack https://bytes.com/topic/db2/answers/431993-safe-cast-string-integer Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Avoid having error when converting String to Integer db2 sql in DB2 up vote 1 down vote favorite I want to select a column that is defined as VARCHAR, as an INTEGER like this: SELECT ID, CAST(Col1 as INT) as MyOutput FROM MyTABLE The problem is when the column has an invalid format (that can't be converted to Integer) I want to set them to NULL. Is there a way to do this in DB2 ? sql db2 sql cast db2 share|improve this question asked Nov 4 '15 at 9:43 Tantaoui El Mehdi 5171929 But why do you want to cast varchar as int? (Every time you want to do such cast, ask yourself if the table design should be updated instread.) –jarlh Nov 4 '15 at 10:04 1 @jarlh the most obvious reason is "because the person writing this query may not have control over the source data". –dan1111 Nov 4 '15 at 10:40 add a comment| 2 Answers 2 active oldest votes up vote 5 down vote accepted One way to accomplish this is to write your own conversion function that handles format exceptions, such as create or replace function to_int_safe (str varchar(20)) returns int deterministic no external action contains sql begin declare continue handler for sqlstate '22018' -- on conversion error return null; return cast(str as int); end then use it in your query: SELECT ID, to_int_safe(Col1) as MyOutput FROM MyTABLE share|improve this answer answered Nov 4 '15 at 12:57 mustaccio 10.7k41933 add a comment| up vote 0 down vote Following case statement determines whether a string contains a number that can be cast to integer. It seems to work so far. Neverthel
your question and get tips & solutions from a community of 418,478 IT Pros & Developers. It's quick & easy. Safe cast from string to integer P: n/a kangaroo Hello, I have a column of type VARCHAR, storing some generally character data. A few of the values in that column are in fact numbers represented as strings (e.g., "12345"). At some moment I need to select those numbers, cast them to INT and do some math on them. Naturally, I do something like this: SELECT INT(MYCOLUMN) WHERE I_BELIEVE_THIS_IS_A_NUMBER='true' However, due to glitches in the logic that writes into MYCOLUMN, sometimes it gets populated with values that are not castable to INT (for example, a string with a carriage return in it). In that case, I get -420 in my stored proc, and the query aborts. What is the best way of rewriting a query like this to make sure that it does not abort during execution? I am okay with INT(MYCOLUMN) returning NULL on a dirty value. I see several options, in order of decreasing preference: 1. Find a native DB2 casting function that would be tolerant of bad charaters (could not find one so far). 2. Write a UDF that'll essentially only take digits from an input string, and then convert that to INT. I am going to fix the logic that writes into MYCOLUMN, to do my best to have only values castable to INT. However, I'd like to see if may be there's a function for 1) above, or if someone sees a better solution. Thanks Bogdan Sheptunov Dec 5 '05 #1 Post Reply Share this Question 7 Replies P: n/a Serge Rielau kangaroo wrote: Hello, I have a column of type VARCHAR, storing some generally character data. A few of the values in that column are in fact numbers represented as strings (e.g., "12345"). At some moment I need to select those numbers, cast them to INT and do some math on them. Naturally, I do something like this: SELECT INT(MYCOLUMN) WHERE I_BELIEVE_THIS_IS_A_NUMBER='t