Varchar2 and Oracle quick question

Question

Hi guys I'm using varchar2 for a product name field, but when I query the database from the run SQL command line it shows too many empty spaces, how can I fix this without changing the datatype

here is the link to the ss

http://img203.imageshack.us/img203/20/varchar.jpg

Varchar2(50), maybe the 50 addes 50 spaces? even if their not used? — , Apr 26 '10 at 01:32

score 1 · Answer 1 · answered Apr 26 '10 at 01:21

1

The data that got inserted into the database (probably through some ETL process) had spaces which were not trimmed.

You could update using (pseudo code)

Update Table Set Column = Trim(Column)

answered Apr 26 '10 at 01:21

Raj Kaimal

8,304
27
18

i did this but it didnt help, its the same – Apr 26 '10 at 01:31

score 0 · Answer 2 · answered Mar 03 '14 at 08:04

0

Hi

Try to use trim on both sides,
Update TableName set FieldName = RTrim(LTrim(FieldName))

Regards

answered Mar 03 '14 at 08:04

user3374033

1

score 0 · Accepted Answer · answered Apr 26 '10 at 02:20

If TRIM does not change the results, that tells you that there are not trailing spaces in the actual database rows; they're just being added as part of the formatted screen output.

By default, sqlplus (the command-line Oracle tool you appear to be using) uses the maximum length of the varchar2 column as the (fixed) width when displaying the results of a select statement.

If you want to change this, use the column format sqlplus command before running the select. For example:

column DEPT_NAME format a20

Varchar2 and Oracle quick question

3 Answers3