oracle 一句话中的最后一个词：在 SQL 中（可能使用正则表达式？）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3908955/
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
Last word in a sentence: In SQL (regular expressions possible?)
提问by Guru
I need this to be done in Oracle SQL (10gR2). But I guess, I would rather put it plainly, any good, efficient algorithm is fine.
我需要在 Oracle SQL (10gR2) 中完成此操作。但我想，我宁愿说白了，任何好的、高效的算法都可以。
Given a line (or sentence, containing one or many words, English), how will you find the last word of the sentence?
给定一行（或句子，包含一个或多个单词，英语），您将如何找到该句子的最后一个单词？
Here is what I have tried in SQL. But, I would like to see an efficient way of doing this.
这是我在 SQL 中尝试过的。但是，我希望看到一种有效的方法来做到这一点。
select reverse(substr(reverse(&p_word_in)
, 0
, instr(reverse(&p_word_in), ' ')
)
)
from dual;
The idea was to reverse the string, find the first occurring space, retrieve the substring and reverse the string. Is it quite efficient? Is a regular expression available? I am on Oracle 10g R2. But I dont mind seeing any attempt in other programming language, I wont mind writing a PL/SQL function if need be.
这个想法是反转字符串，找到第一个出现的空间，检索子字符串并反转字符串。是不是很有效率？正则表达式是否可用？我在 Oracle 10g R2 上。但我不介意看到其他编程语言的任何尝试，如果需要，我不介意编写 PL/SQL 函数。
Update:
更新：
Jeffery Kemp has given a wonderful answer. This works perfectly.
Jeffery Kemp 给出了精彩的答案。这完美地工作。
Answer
回答
SELECT SUBSTR(&sentence, INSTR(&sentence,' ',-1) + 1)
FROM dual
回答by Jeffrey Kemp
I reckon it's simpler with INSTR/SUBSTR:
我认为使用 INSTR/SUBSTR 更简单：
WITH q AS (SELECT 'abc def ghi' AS sentence FROM DUAL)
SELECT SUBSTR(sentence, INSTR(sentence,' ',-1) + 1)
FROM q;
回答by Wolph
Not sure how it is performance wise, but this should do it:
不确定它的性能如何，但这应该做到：
select regexp_substr(&p_word_in, '\S+$') from dual;
回答by Jesse
I'm not sure if you can use a regex in oracle, but wouldn't
我不确定你是否可以在 oracle 中使用正则表达式，但不会
(\w+)\W*$
(\w+)\W*$
work?
工作？
回答by Harrison
this leaves the punctuation but gets the final word
这留下了标点符号，但得到了最后的话
with datam as (
SELECT 'abc asdb.' A FROM DUAL UNION
select 'ipso factum' a from dual union
select 'ipso factum' a from dual union
SELECT 'ipso factum2' A FROM DUAL UNION
SELECT 'ipso factum!' A FROM DUAL UNION
SELECT 'ipso factum !' A FROM DUAL UNION
SELECT 'ipso factum/**//*/?.?' A FROM DUAL UNION
SELECT 'ipso factum ...??!?!**' A FROM DUAL UNION
select 'ipso factum ..d.../.>' a from dual
)
SELECT a,
--REGEXP_SUBSTR(A, '[[:alnum:]]_+$', 1, 1, 'c') , /** these are the other examples*/
--REGEXP_SUBSTR(A, '\S+$') , /** these are the other examples*/
regexp_substr(a, '[a-zA-Z]+[^a-zA-Z]*$')
from datam
回答by compie
This regex matches the last word on a line:
此正则表达式匹配一行中的最后一个单词：
\w+$
And RegexBuddy gives this code for use in Oracle:
RegexBuddy 给出了在 Oracle 中使用的代码：
DECLARE
match VARCHAR2(255);
BEGIN
match := REGEXP_SUBSTR(subject, '[[:alnum:]]_+$', 1, 1, 'c');
END;
