iSQL> CREATE TABLE T (NO CHAR(1));
Create success.
iSQL> INSERT INTO T VALUES('1');
1 row inserted.
iSQL> INSERT INTO T VALUES('2');
1 row inserted.
iSQL> SELECT * FROM T WHERE NO = 1; -- Compare the value of the CHAR type column with the numeric data. NO column value is automatically converted to numeric type.
NO -- Since only values that can be converted to numeric type exist in the NO column, it is executed without error.
------
1
1 row selected.
iSQL> INSERT INTO T VALUES('A'); -- A value that cannot be converted to a numeric type is entered in the NO column.
1 row inserted.
iSQL> SELECT * FROM T WHERE NO = 1; -- As the value of the NO column is automatically converted into a numeric type, an invalid literal error occurs due to the value of'A'.
[ERR-21011 : Invalid literal]
iSQL> SELECT * FROM T WHERE NO = '1'; -- For character columns, single quotation marks (') must be used for comparison values.
NO
------
1
1 row selected.
iSQL> INSERT INTO T VALUES(' '); -- An Invalid literal error occurs even if the NO column contains blank characters.
1 row inserted.
iSQL> SELECT TO_NUMBER(NO) FROM T;
[ERR-21011 : Invalid literal]
iSQL> SELECT TO_NUMBER(TRIM(NO)) FROM T; -- In the case of space characters, the error can be eliminated by using the TRIM function.
TO_NUMBER(TRIM(CODE))
------------------------
1
2
3 rows selected.
iSQL> |