SQL 在 Oracle 上使用 substr 修剪字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24261915/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using substr to trim string on Oracle
提问by Michal
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.
我想将字符串修剪到指定的长度。如果字符串更短，我不想做任何事情。我找到了一个函数 substr() 来完成这项工作。但是，如果字符串比最大长度短，Oracle 文档中没有任何内容。
For example this:
例如这个：
select substr('abc',1,5) from dual;
returns 'abc', which is what I need.
返回'abc'，这是我需要的。
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?
我想问一下这是否安全，因为该函数似乎没有为此用途定义。有没有更好的方法来截断？
回答by neshkeev
It is totally ok, but if you want, you can use this query:
完全没问题，但如果你愿意，你可以使用这个查询：
select substr('abc',1,least(5,length('abc'))) from dual;
回答by Gordon Linoff
This is an interesting question. Surprisingly, the documentationdoesn'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.
我认为你在做什么是很安全的。 substr()当字符串太短时，不会在字符串的末尾“添加”字符。随着时间的推移，我在许多数据库（包括 Oracle）中依赖于这种行为。这就是类似函数在其他数据库和大多数语言中的工作方式。
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.
一种例外情况是原始数据类型是 achar()而不是varchar2()类型。在这种情况下，该函数将返回一个相同类型的字符串，因此它可能用空格填充。但是，那是类型的属性，而不是函数的属性。
回答by Nishanthi Grashia
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.
上面的查询将采用字符串的长度或数字 5，以较小者为准。
回答by Bob Jarvis - Reinstate Monica
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:
如果您想绝对确定单独使用 SUBSTR 不会导致尾随空白（您不会，但有时确实很确定），您可以使用：
SELECT RTRIM(SUBSTR('abc',1,5)) FROM DUAL;
Share and enjoy.
分享和享受。
