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?
It is totally ok, but if you want, you can use this query:
select substr('abc',1,least(5,length('abc'))) from dual;
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.
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 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.
