CHAR compared to VARCHAR2

View All Public Scripts Login to Run Script

Script Name CHAR compared to VARCHAR2
Visibility Unlisted - anyone with the share link can access
Description VARCHAR2 selected values have trailing spaces after being compared to CHAR ?
Contributor Kim Berg Hansen
Created Thursday September 07, 2017

Statement 1

create table ch ( ch char(10) )

Table created.

Statement 2
```
drop table ch purge
```
```
Table dropped.
```

Statement 3

create table ch ( ch char(10) )

Table created.

Statement 4

create table vc ( vc varchar2(10) )

Table created.

Statement 5
```
drop table vc purge
```
```
Table dropped.
```
Statement 6
```
drop table ch purge
```
```
Table dropped.
```

Statement 7

create table ch ( ch char(10) )

Table created.

Statement 8

create table vc ( vc varchar2(10) )

Table created.

Statement 9

insert into ch values ('MICKEY')

1 row(s) inserted.

Statement 10

insert into ch values ('MICKEY  ')

1 row(s) inserted.

Statement 11

insert into ch values ('MICKEY    ')

1 row(s) inserted.

Statement 12

insert into vc values ('MICKEY')

1 row(s) inserted.

Statement 13

insert into vc values ('MICKEY  ')

1 row(s) inserted.

Statement 14

insert into vc values ('MICKEY    ')

1 row(s) inserted.

Statement 15
```
commit
```
```
Statement processed.
```

Statement 16

select ch, dump(ch) as ch_dump 
from ch


CH CH_DUMP
MICKEY    Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32
MICKEY    Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32
MICKEY    Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32

3 rows selected.

CH	CH_DUMP
MICKEY	Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32
MICKEY	Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32
MICKEY	Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32

Statement 17

select vc, dump(vc) as vc_dump 
from vc


VC VC_DUMP
MICKEY Typ=1 Len=6: 77,73,67,75,69,89
MICKEY  Typ=1 Len=8: 77,73,67,75,69,89,32,32
MICKEY    Typ=1 Len=10: 77,73,67,75,69,89,32,32,32,32

3 rows selected.

VC	VC_DUMP
MICKEY	Typ=1 Len=6: 77,73,67,75,69,89
MICKEY	Typ=1 Len=8: 77,73,67,75,69,89,32,32
MICKEY	Typ=1 Len=10: 77,73,67,75,69,89,32,32,32,32

Statement 18

select ch, dump(ch) as ch_dump 
     , vc, dump(vc) as vc_dump 
from ch 
join vc 
   on vc.vc = ch.ch


CH CH_DUMP VC VC_DUMP
MICKEY    Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32 MICKEY    Typ=1 Len=10: 77,73,67,75,69,89,32,32,32,32
MICKEY    Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32 MICKEY    Typ=1 Len=10: 77,73,67,75,69,89,32,32,32,32
MICKEY    Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32 MICKEY    Typ=1 Len=10: 77,73,67,75,69,89,32,32,32,32

3 rows selected.

CH	CH_DUMP	VC	VC_DUMP
MICKEY	Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32	MICKEY	Typ=1 Len=10: 77,73,67,75,69,89,32,32,32,32
MICKEY	Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32	MICKEY	Typ=1 Len=10: 77,73,67,75,69,89,32,32,32,32
MICKEY	Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32	MICKEY	Typ=1 Len=10: 77,73,67,75,69,89,32,32,32,32

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database