on 01-19-2010 7:28 PM
Hi,
if it used zero-length string as a value for INSERT INTO statement, it produces error.
Is it possible insert/update zero-length string? How?
Thanks.
> Hi,
> if it used zero-length string as a value for INSERT INTO statement, it produces error.
> Is it possible insert/update zero-length string? How?
What kind of error do you get?
Why shouldn't it work?
sqlcli db76=> create table aaa (col1 varchar(10), col2 varchar(10))
0 rows affected
sqlcli db76=> insert into aaa values ('', '')
1 row affected
sqlcli db76=> select count(*) from aaa
| EXPRESSION1 |
| ------------------ |
| 1 |
1 row selected
sqlcli db76=> select * from aaa
| COL1 | COL2 |
| ---------- | ---------- |
| | |
1 row selected
sqlcli db76=> select * from aaa where col1 =''
| COL1 | COL2 |
| ---------- | ---------- |
| | |
1 row selected
And yes, it's a real zero-length string and not a NULL value as it is in Oracle.
But only if your SQL mode is INTERNAL.
sqlcli db76=> select * from aaa where col1 is null
* 0:
Don't see any errors here...
Let's switch to ORACLE sql mode:
sqlcli db76=> \m ORACLE
SQL mode changed to ORACLE
sqlcli db76=> select * from aaa where col1=''
* 0:
The empty string ('') is now handled as NULL, so the zero-length string does not match anymore.
sqlcli db76=> select * from aaa where col1 is null
* 0:
sqlcli db76=> select * from aaa
| COL1 | COL2 |
| ---------- | ---------- |
| | |
1 row selected (0 usec)
Still the values in the table are not NULL values now.
Let's make up a ORACLE mode NULL value:
sqlcli db76=> insert into aaa values ('', 'X')
1 row affected
sqlcli db76=> select * from aaa
| COL1 | COL2 |
| ---------- | ---------- |
| | |
| ? | X |
2 rows selected
sqlcli db76=> select * from aaa where col1 is null
| COL1 | COL2 |
| ---------- | ---------- |
| ? | X |
1 row selected
regards,
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
x
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.