Using substr to trim string on Oracle

Question

I want to trim a string to a specified length. If the string is shorter, I don't want to do anything. I found a function substr() which does the job. However there is nothing in the Oracle documentation what happens if the string is shorter, than maximal length.

For example this:

select substr('abc',1,5) from dual;

returns 'abc', which is what I need.

I'd like to ask if this is safe, because the function seems not to be defined for this usage. Is there a better way how to truncate?

Solution 1

This is an interesting question. Surprisingly, the documentation doesn't seem to cover this point explicitly.

I think what you are doing is quite safe. substr() is not going to "add" characters to the end of the string when the string is too short. I have depended on this behavior in many databases, including Oracle, over time. This is how similar functions work in other databases and most languages.

The one sort-of-exception would be when the original data type is a char() rather than varchar2() type. In this case, the function would return a string of the same type, so it might be padded with spaces. That, though, is a property of the type not really of the function.

Solution 2

It is totally ok, but if you want, you can use this query:

select substr('abc',1,least(5,length('abc'))) from dual;

Solution 3

If you want to be absolutely certain that you won't end up with trailing blanks by using SUBSTR alone (you won't, but sometimes it's comforting be really sure) you can use:

SELECT RTRIM(SUBSTR('abc',1,5)) FROM DUAL;

Share and enjoy.

Solution 4

It is better to use the below query

SELECT SUBSTR('abc',1,LEAST(5,LENGTH('abc'))) FROM DUAL;

Above query would either take the length of the string or the number 5 whichever is lower.

View more solutions

Comments

[ngrashia]

I meant, to be fool-proof and to be rest-assured without any doubts!