How to make use of SQL (Oracle) to count the size of a string?

Question

i was wondering if there was a function in Oracle to count the number of character size in Oracle, i.e. given "Burger", the SQL returns 6.

i.e. select XXX('Burger') from DUAL; 

Solution 1

You can use LENGTH() for CHAR / VARCHAR2 and DBMS_LOB.GETLENGTH() for CLOB. Both functions will count actual characters (not bytes).

See the linked documentation if you do need bytes.

Solution 2

you need length() function

select length(customer_name) from ar.ra_customers

Solution 3

The length function will do it. See http://www.techonthenet.com/oracle/functions/length.php

Solution 4

Regarding to Your example

select length('Burger') from dual;

I hope this will help :)

View more solutions

Comments

[Wayne]

I have, on occasion, seen systems that pad with blank spaces to make data in a column uniform length and had to use LENGTH(TRIM()) to get actual value lengths.

[Frank Schmitt]

@Wayne That's usually the case for CHAR columns - CHAR columns are always padded to the defined length, whereas VARCHAR columns aren't.

[Wayne]

@FrankSchmitt I realize that but thought it might be helpful to some people since you don't need the white space when querying a CHAR and the data type is not always obvious without checking the schema.

[jpmc26]

What about NCHAR and NVARCHAR2?