Get column value length, not column max length of value

oracle string-length

Buksy · Oct 25, 2012 · Viewed 126.4k times · Source

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

Answer

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.

Get column value length, not column max length of value

Answer

Related questions