19

How do I get actual length of value in column?

My (oracle) sql command returns maximum length of value that can be inserted in the column instead of real length of value.

How to fix this?

SQL> SELECT typ, length(t1.typ)
     FROM AUTA_VIEW t1;

TYP        LENGTH(T1.TYP)
---------- --------------
BMW                    10
BMW                    10
BMW                    10
Ferrari                10
BMW                    10
Audi                   10
Audi                   10
Audi                   10
Ferrari                10
Ferrari                10
Mercedes               10
Buksy
  • 11,571
  • 9
  • 62
  • 69

1 Answers1

28

LENGTH() does return the string length (just verified). I suppose that your data is padded with blanks - try 

SELECT typ, LENGTH(TRIM(t1.typ))
FROM AUTA_VIEW t1;

instead.

As OraNob mentioned, another cause could be that CHAR is used in which case LENGTH() would also return the column width, not the string length. However, the TRIM() approach also works in this case.

Andreas Fester
  • 36,091
  • 7
  • 95
  • 123