Using substr to trim string on Oracle

sql oracle plsql substr
Michal Krasny picture Michal Krasny · Jun 17, 2014 · Viewed 49.1k times · Source

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?

Answer

neshkeev picture neshkeev · Jun 17, 2014

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

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

Related questions

Remove all characters after a specific character in PL/SQL

How do I get a substring from this example value: john.abc_1234 I want it to return john.abc.So basically we need to remove all the information after _. More examples: 1234_abc

Read More
SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More