![]() Let’s say you have a table called FUND_ACCOUNT that has two columns: AID_YEAR char(4)Īnd let’s say that you want to modify the OFFICE_ID to be numeric, but that there are existing rows in the table, and even worse, some of those rows have an OFFICE_ID value of ‘ ‘ (blank). If you do an insert lect * from.statement, it’s easy to get the ‘Invalid Number’ error as well. Select string_column from TABLE where string_column <= ‘b’ Select string_column from TABLE where string_column > ‘1’ Select string_column from TABLE where string_column between ‘2’ and ‘z’ So the numerical operations/functions can be applied on the Strings, and these queries work: ![]() On the other hand, Oracle can treat Strings as numbers. And the explicit delimiting of the string-numbers resolved the problem. Although not numerical, the invalid number was reported. ![]() …and voilà: It returns the expected result.Īnd indeed: the col acc_num in my table is defined as String. Select max(acc_num) from ACCOUNTS where acc_num between '1001000' and '1001999' I have just surrounded the «numerical» values, to make them ‘Strings’, just making them explicitly delimited: That one presented a problem: Error: ORA-01722: invalid number Select max(acc_num) from ACCOUNTS where acc_num between 10010999 ![]() Oracle does automatic String2number conversion, for String column values! However, for the textual comparisons in SQL, the input must be delimited as a String explicitly: The opposite conversion number2String is not performed automatically, not on the SQL-query level. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |