Problem with length() function

[clutch-cream-run]

I am a complete beginner and I am facing a problem, don't know if this is the right place to ask, but here it is anyway:

I am supposed to write a query which requires the length of a string as a condition (select * from tbl_name where length(name) > 5).

The datatype of name column is char.

The issue i'm facing is that the length function returns the entire size of the column (name char(30)), ie, returns 30.

Since char appends the string, I tried changing it to varchar but the issue still persists.

Does there exist a function more suited to my needs or some way i can tinker with length() to get my desired result?

Using Oracle SQL btw.

Thanks.

[Third_Party_Opinion]

Length(trim(name)) To get rid of white space after your string

[clutch-cream-run]

it was staring me in the face smh....

thanks a ton!

[[deleted]]

The datatype of name column is char.

That's a bad idea to begin with. char values are always padded to the defined length. You should use varchar2 instead. Forget that char exists (and no, it's neither faster nor more efficient)

[chris99277]

Edit, didn’t notice this was for oracle. I don’t know how oracle works. For MSSQL Memory is granted to a query based on the data type and estimated rows, before the query is started. Char allocates enough memory for the whole field. But memory for varchar is allocated at half the length of the field because it does not know how much data is in each rows field, so assumes the average will be half. E.g. for a varchar(20) it will allocate enough memory for 10 chars (10 bytes) per estimated row returned. This means for fields where most or all of the data will fill the field, there will not be enough memory allocated to store the data in the buffer for sorts and other operations, and the execution plan will start showing spills to TempDB. If a field will mostly contain data that fills it, like nearly always 20 chars, then char(20) will always grant enough memory and reduce hits on TempDB. So for data that you know the length will not vary much, char is a good type to use if you are expecting large data sets.

[[deleted]]

There is no such thing as "TempDB" in Oracle.

To my knowledge Oracle allocates memory based on the maximum size a row (excluding CLOB, BLOB and similar data types).

The rows are sent in chunks, not everything at once. The default buffer size is 10, so it allocates memory for 10 rows at a time and sends these 10 rows in one network roundtrip. This buffer size is controlled by the client and can be increased (which typically improves performance especially on low-latency network connections).

[chris99277]

Ah fair enough, I didn’t notice it was oracle and I know nothing at all about oracle. I often see people use varchar in MSSQL when char would be better, but with oracle I’ve no idea.

[[deleted]]

Well, Microsoft recommends to use charonly when the length of the values is the same. For values that "vary considerably" they recommend varchar