SQL 如何在oracle中获得字符串最右边的10个位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3888326/
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
How to get rightmost 10 places of a string in oracle
提问by tismon
I am trying to fetch an id from an oracle table. It's something like TN0001234567890345. What I want is to sort the values according to the right most 10 positions (e.g. 4567890345). I am using Oracle 11g. Is there any function to cut the rightmost 10 places in Oracle SQL ?
我正在尝试从 oracle 表中获取一个 id。有点像TN0001234567890345. 我想要的是根据最右边的 10 个位置（例如4567890345）对值进行排序。我正在使用 Oracle 11g。是否有任何功能可以削减 Oracle SQL 中最右边的 10 个位置？
Thanks in advance
提前致谢
tismon
蒂斯蒙
回答by codaddict
回答by Briguy37
codaddict's solution works if your string is known to be at least as long as the length it is to be trimmed to. However, if you could have shorter strings (e.g. trimming to last 10 characters and one of the strings to trim is 'abc') this returns null which is likely not what you want.
如果已知您的字符串至少与要修剪的长度一样长，则 codacci 的解决方案有效。但是，如果您可以使用更短的字符串（例如修剪到最后 10 个字符并且要修剪的字符串之一是 'abc'），这将返回 null，这可能不是您想要的。
Thus, here's the slightly modified version that will take rightmost 10 characters regardless of length as long as they are present:
因此，这是稍微修改的版本，只要它们存在，无论长度如何，都将采用最右边的 10 个字符：
select substr(colName, -least(length(colName), 10)) from tableName;
回答by user3496712
Another way of doing it though more tedious. Use the REVERSEand SUBSTRfunctions as indicated below:
另一种方法虽然更乏味。使用REVERSE和SUBSTR功能如下所示：
SELECT REVERSE(SUBSTR(REVERSE('TN0001234567890345'), 1, 10)) FROM DUAL;
The first REVERSE function will return the string 5430987654321000NT.
第一个 REVERSE 函数将返回字符串5430987654321000NT。
The SUBSTRfunction will read our new string 5430987654321000NTfrom the first character to the tenth character which will return 5430987654.
该SUBSTR函数5430987654321000NT将从第一个字符到第十个字符读取我们的新字符串，这将返回 5430987654。
The last REVERSEfunction will return our original string minus the first 8 characters i.e. 4567890345
最后一个REVERSE函数将返回我们的原始字符串减去前 8 个字符，即4567890345
回答by Gary_W
Yeah this is an old post, but it popped up in the list due to someone editing it for some reason and I was appalled that a regular expression solution was not included! So here's a solution using regex_substr in the order by clause just for an exercise in futility. The regex looks at the last 10 characters in the string:
是的，这是一篇旧帖子，但由于有人出于某种原因对其进行了编辑，它突然出现在列表中，我很震惊没有包含正则表达式解决方案！所以这里有一个在 order by 子句中使用 regex_substr 的解决方案，只是为了徒劳的练习。正则表达式查看字符串中的最后 10 个字符：
with tbl(str) as (
select 'TN0001239567890345' from dual union
select 'TN0001234567890345' from dual
)
select str
from tbl
order by to_number(regexp_substr(str, '.{10}$'));
An assumption is made that the ID part of the string is at least 10 digits.
假设字符串的 ID 部分至少为 10 位数字。
