SQL 拆分字符串并取最后一个元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14412898/
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
Split string and take last element
提问by Antonio Papa
I have a table with this values:
我有一个包含此值的表：
Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx
OurStory/MeettheFoodieandtheMD.aspx
TheFood/OurMenu.aspx
I want to get this
我想得到这个
Diet.aspx
MeettheFoodieandtheMD.aspx
OurMenu.aspx
How can i do this?
我怎样才能做到这一点？
回答by jazzytomato
The way to do it in SQL :
在 SQL 中执行此操作的方法：
SELECT SUBSTRING( string , LEN(string) - CHARINDEX('/',REVERSE(string)) + 2 , LEN(string) ) FROM SAMPLE;
JSFiddle here http://sqlfiddle.com/#!3/41ead/11
JSFiddle在这里http://sqlfiddle.com/#!3/41ead/11
回答by John Woo
SELECT REVERSE(LEFT(REVERSE(columnName), CHARINDEX('/', REVERSE(columnName)) - 1))
FROM tableName
ORHER SOURCE(s)
ORHER 来源
回答by TechDo
Please try:
请尝试：
select url,(CASE WHEN CHARINDEX('/', url, 1)=0 THEN url ELSE RIGHT(url, CHARINDEX('/', REVERSE(url)) - 1) END)
from(
select 'Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx' as url union
select 'OurStory/MeettheFoodieandtheMD.aspx' as url union
select 'MeettheFoodieandtheMD.aspx' as url
)xx
回答by shubham
Please try the code below:
请尝试以下代码：
SELECT SUBSTRING( attachment, LEN(attachment)
- CHARINDEX('/', REVERSE(attachment)) + 2, LEN(attachment) ) AS filename
FROM filestable;
回答by Max
Try this. It's easier.
尝试这个。这更容易。
SELECT RIGHT(string, CHARINDEX('/', REVERSE(string)) -1) FROM TableName
SELECT RIGHT(string, CHARINDEX('/', REVERSE(string)) -1) FROM TableName
回答by ank2k11
Create Table #temp
(
ID int identity(1,1) not null,
value varchar(100) not null
)
DECLARE @fileName VARCHAR(100);
INSERT INTO #temp(value) SELECT value from STRING_SPLIT('C:\Users\Documents\Datavalidation\Input.csv','\')
SET @fileName=(SELECT TOP 1 value from #temp ORDER BY ID DESC);
SELECT @fileName AS File_Name;
DROP TABLE #temp
回答by ktaria
more simple and elegant :
更简单优雅：
select reverse(SPLIT_PART(reverse('Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx'), '/',1))
回答by SoftCreator
SELECT REVERSE((SELECT TOP 1 value FROM STRING_SPLIT(REVERSE('Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx'), '/' ))) AS fName
Result: Diet.aspx
Standard STRING_SPLIT does not allow to take last value.
标准 STRING_SPLIT 不允许采用最后一个值。
The trick is to reverse the string (REVERSE) before splitting with STRING_SPLIT, get the first value from the end (TOP 1 value) and then the result needs to be reversed again (REVERSE) to restore the original chars sequence.
诀窍是在用 STRING_SPLIT 拆分之前反转字符串（REVERSE），从末尾获取第一个值（TOP 1 值），然后需要再次反转结果（REVERSE）以恢复原始字符序列。
Here is the common approach, when working with SQL table:
这是使用 SQL 表时的常用方法：
SELECT REVERSE((SELECT TOP 1 VALUE FROM STRING_SPLIT(REVERSE(mySearchString) , '/' ))) AS myLastValue FROM myTable
回答by langus
I corrected jazzytomato's solution for single character tokens (D) and for single tokens (Diet.aspx)
我更正了 jazzytomato 针对单个字符标记 ( D) 和单个标记 ( Diet.aspx)的解决方案
SELECT SUBSTRING( string , LEN(string) - CHARINDEX('/','/'+REVERSE(string)) + 2 , LEN(string) ) FROM SAMPLE;
回答by Abhishek Kanrar
reverse(SUBSTRING(reverse(yourString),0,CHARINDEX('/',reverse(yourString)))) as stringLastPart
