SQL 获取'/'字符后的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32381860/
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 after '/' character
提问by Rudy Herdez
I want to extract the string after the character '/' in a PostgreSQL SELECT query.
我想在 PostgreSQL SELECT 查询中提取字符 '/' 之后的字符串。
The field name is source_path, table name is movies_history.
字段名是source_path，表名是movies_history。
Data Examples:
数据示例：
Values for source_path:
source_path 的值：
- 184738/file1.mov
- 194839/file2.mov
- 183940/file3.mxf
- 118942/file4.mp4
- 184738/file1.mov
- 194839/file2.mov
- 183940/file3.mxf
- 118942/file4.mp4
And so forth. All the values for source_path are in this format
等等。source_path 的所有值都是这种格式
- random_number/filename.xxx
- random_number/文件名.xxx
I need to get 'file.xxx' string only.
我只需要获取 'file.xxx' 字符串。
回答by Erwin Brandstetter
If your case is that simple (exactlyone /in the string) use split_part():
如果您的情况那么简单（字符串中正好有一个/），请使用split_part()：
SELECT split_part(source_path, '/', 2) ...
Ifthere can be multiple/, and you want the string after the lastone, a simple and fast solution would be to process the string backwards with reverse(), take the first part, and reverse()a 2nd time:
如果可以有多个/，并且您想要最后一个之后的字符串，一个简单而快速的解决方案是使用 向后处理字符串reverse()，取第一部分和reverse()第二次：
SELECT reverse(split_part(reverse(source_path), '/', 1)) ...
Oryou could use the more versatile (and more expensive) substring()with a regular expression:
或者您可以使用更通用（且更昂贵）substring()的正则表达式：
SELECT substring(source_path, '[^/]*$') ...
Explanation:
解释：
[...].. encloses a list of characters to form a character class.[^...].. if the list starts with ^it's the inversion(all characters not in the list).*.. quantifier for 0-n times.$.. anchor to end of string.
[...].. 包含一个字符列表以形成一个字符类。[^...].. 如果列表以^它的倒置开头（所有不在列表中的字符）。*.. 0-n 次的量词。$.. 锚定到字符串的末尾。
回答by Juan Carlos Oropeza
You need use substringfunction
您需要使用子字符串函数
SELECT substring('1245487/filename.mov' from '%/#"%#"%' for '#');
Explanation:
解释：
%/
This mean %some text and then a /
这意味着%一些文本，然后是/
#"%#"
each #is the place holder defined in the last part for '#'and need and aditional "
每个#都是最后一部分中定义的占位符for '#'，需要和附加"
So you have <placeholder> % <placeholder>and function will return what is found inside both placeholder. In this case is %or the rest of the string after /
所以你有<placeholder> % <placeholder>并且函数将返回在两个占位符中找到的内容。在这种情况下是%或之后的字符串的其余部分/
FINAL QUERY:
最终查询：
SELECT substring(source_path from '%/#"%#"%' for '#');
FROM movies_history
