删除 PL/SQL 中特定字符后的所有字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7717566/
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
Remove all characters after a specific character in PL/SQL
提问by sandy
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 _.
我希望它返回john.abc。所以基本上我们需要删除之后的所有信息_。
More examples: 1234_abc
更多示例：1234_abc
回答by Adriano Carneiro
You can use SUBSTRand INSTR:
您可以使用SUBSTR和INSTR：
select substr('john.abc_1234', 1, instr('john.abc_1234', '_') -1)
from dual
Warning: This is only guaranteed to work if your string actually has an underscore in it
警告：只有当您的字符串中确实有下划线时，这才保证有效
Update
更新
Additionally, if you are running from Oracle 10g on, you could take the Regex path, which would more powerfully handle exceptions.
此外，如果您从 Oracle 10g 开始运行，您可以采用 Regex 路径，它可以更强大地处理异常。
Here are some links on how to do it in Oracle:
以下是有关如何在 Oracle 中执行此操作的一些链接：
回答by sparklos
You can use this monstrosity, when you're not sure if all your values contain the character you want to cut off your string at.
当您不确定所有值是否包含要切断字符串的字符时，您可以使用这种怪物。
SELECT
(CASE WHEN INSTR(field, '_') > 0
THEN substr(field, 1, instr(field, '_') -1)
ELSE field
END) AS field
FROM
dual
回答by Himanshu Ahuja
You can use via a simple combination of substr(..)and instr(...)to find the string which is free from underscorespecial charecter. Also in order to only select _containing strings you can make use of additional whereclause as below
您可以使用substr(..)和的简单组合instr(...)来查找没有underscore特殊字符的字符串。此外，为了只选择_包含字符串，您可以使用如下附加where子句
select substr('john.abc_1234', 1,
instr('john.abc_1234', '_') -1)
from dual where
instr('john.abc_1234', '_')>0
回答by kmeow1024
This guy figured it out! http://programcsharp.com/blog/post/strip-non-numeric-characters-from-a-string-in-sql-server
这家伙想通了！ http://programcsharp.com/blog/post/strip-non-numeric-characters-from-a-string-in-sql-server
SELECT
(SELECT CAST(CAST((
SELECT SUBSTRING(FieldToStrip, Number, 1)
FROM master..spt_values
WHERE Type='p' AND Number <= LEN(FieldToStrip) AND
SUBSTRING(FieldToStrip, Number, 1) LIKE '[0-9]' FOR XML Path(''))
AS xml) AS varchar(MAX)))
FROM
SourceTable
