oracle 从右侧获取字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3440849/
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
get string from right hand side
提问by Hyman
I am writing a query in Oracle.
我正在 Oracle 中编写查询。
I want to get a string from the right hand side but the string length is dynamic.
我想从右侧获取一个字符串，但字符串长度是动态的。
Ex:
前任：
299123456789
299123456789
I want to get 123456789
我要获取 123456789
substr(PHONE_NUMBERS,-X,Y)
X is different for each record.
每条记录的 X 都不同。
I tried this:
我试过这个：
substr(PHONE_NUMBERS,-length(PHONE_NUMBERS),Y)
and it didn't work..
它没有用..
How can I write this query?
我该如何编写此查询？
回答by Sanjay Kattimani
If you want to list last 3 chars, simplest way is
如果要列出最后 3 个字符，最简单的方法是
select substr('123456',-3) from dual;
回答by Bharat
SQL> select substr('123456',-1,6) from dual;
S
-
6
SQL> select substr('123456',-6,6) from dual;
SUBSTR
------
123456
SQL> select substr('123456',-7,6) from dual;
S
-
If you watch above statements, 3 query gives null value as -7 > length('123456').
如果你看上面的语句，3 查询给出空值 -7 > length('123456')。
So check the length of CONT_PHONE_NUMBERSand PHONE_NUMBERS
因此，检查的长度CONT_PHONE_NUMBERS和PHONE_NUMBERS
Hope this helps you
希望这对你有帮助
回答by Adam Musch
SQL> select substr('999123456789', greatest (-9, -length('999123456789')), 9) as value from dual;
VALUE
---------
123456789
SQL> select substr('12345', greatest (-9, -length('12345')), 9) as value from dual;
VALUE
----
12345
The call to greatest (-9, -length(string))limits the starting offset either 9 characters left of the end or the beginning of the string.
调用greatest (-9, -length(string))将起始偏移量限制在末尾或字符串开头的左边 9 个字符处。
回答by TF Krog
I just found out that regexp_substr()is perfect for this purpose :)
我刚刚发现这regexp_substr()非常适合此目的:)
My challenge is picking the right-hand 16 chars from a reference string which theoretically can be everything from 7ish to 250ish chars long. It annoys me that substr( OurReference , -16 )returns nullwhen length( OurReference ) < 16. (On the other hand, it's kind of logical, too, that Oracle consequently returns nullwhenever a call to substr()goes beyond a string's boundaries.) However, I can set a regular expression to recognise everything between 1 and 16 of any char right before the end of the string:
我的挑战是从参考字符串中选择右侧的 16 个字符，理论上可以是从 7 到 250 字符长的所有字符。什么时候substr( OurReference , -16 )返回让我很恼火。（另一方面，这也是合乎逻辑的，每当调用超出字符串的边界时，Oracle就会返回。）但是，我可以设置一个正则表达式来识别任何字符的 1 到 16 之间的所有内容。字符串：nulllength( OurReference ) < 16nullsubstr()
regexp_substr( OurReference , '.{1,16}$' )
When it comes to performance issues regarding regular expressions, I can't say which of the GREATER()solution and this one performs best. Anyone test this?Generally I've experienced that regular expressions are quite fast if they're written neat and well (as this one).
当涉及到有关正则表达式的性能问题时，我不能说哪个GREATER()解决方案和这个解决方案性能最好。有人测试这个吗？一般来说，我已经体验到，如果正则表达式写得干净利落（就像这个），它们会非常快。
Good luck! :)
祝你好运！:)
回答by Joe User
substr(PHONE_NUMBERS, length(PHONE_NUMBERS) - 3, 4)
回答by Mahendra Mustika Wijaya
the pattern maybe looks like this :
模式可能是这样的：
substr(STRING, ( length(STRING) - (TOTAL_GET_LENGTH - 1) ),TOTAL_GET_LENGTH)
in your case , it will like this :
在你的情况下，它会像这样：
substr('299123456789', (length('299123456789')-(9 - 1)),9)
substr('299123456789', (12-8),9)
substr('299123456789', 4,9)
the result ? of course '123456789'
the length is dynamic , voila :)
长度是动态的，瞧:)
回答by Jivan Miranda Rodriguez
SELECT SUBSTR('299123456789',DECODE(least(LENGTH('299123456789'),9),9,-9,LENGTH('299123456789')*-1)) value from dual
Gives 123456789
给 123456789
The same statement works even when the number is less than 9 digits:
即使数字少于 9 位，同样的语句也能工作：
SELECT SUBSTR('6789',DECODE(least(LENGTH('6789'),9),9,-9,LENGTH('6789')*-1)) value from dual
Gives 6789
给 6789
回答by Clayton
I Had the same problem. This worked for me:
我有同样的问题。这对我有用：
CASE WHEN length(sp.tele_phone_number) = 10 THEN
SUBSTR(sp.tele_phone_number,4)
回答by JoeC
Simplest solution:
最简单的解决方案：
substr('299123456',-6,6)
