Using substr to trim string on Oracle

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?

This is an interesting question. Surprisingly, the [documentation][1] 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.


[1]:

[To see links please register here]

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.

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.

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

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