我有一个包含以下数值的表格:
Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx
OurStory/MeettheFoodieandtheMD.aspx
TheFood/OurMenu.aspx
我想获得这个
Diet.aspx
MeettheFoodieandtheMD.aspx
OurMenu.aspx
我该怎么做?
我有一个包含以下数值的表格:
Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx
OurStory/MeettheFoodieandtheMD.aspx
TheFood/OurMenu.aspx
我想获得这个
Diet.aspx
MeettheFoodieandtheMD.aspx
OurMenu.aspx
我该怎么做?
在SQL中完成此操作的方法:
SELECT SUBSTRING( string , LEN(string) - CHARINDEX('/',REVERSE(string)) + 2 , LEN(string) ) FROM SAMPLE;
JSFiddle在这里 http://sqlfiddle.com/#!3/41ead/11
substring(Name, 1, len(string)-CHARINDEX(' ', reverse(string)))
。 - Jeff BradySELECT REVERSE(LEFT(REVERSE(columnName), CHARINDEX('/', REVERSE(columnName)) - 1))
FROM tableName
其他来源
试试这个。它更容易。
SELECT RIGHT(string, CHARINDEX('/', REVERSE(string)) -1) FROM TableName
请尝试以下方法:
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
SELECT REVERSE ((
SELECT TOP 1 value FROM STRING_SPLIT(REVERSE('Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx'), '/')
)) AS fName
Result: Diet.aspx
标准的STRING_SPLIT
不允许获取最后一个值。
技巧是在使用STRING_SPLIT
之前反转字符串(REVERSE
),从末尾获取第一个值(TOP 1 value
),然后再次反转结果(REVERSE
)以恢复原始字符顺序。
以下是在处理SQL表时常用的方法:
SELECT REVERSE ((
SELECT TOP 1 VALUE FROM STRING_SPLIT(REVERSE(mySearchString), '/')
)) AS myLastValue
FROM myTable
SELECT TOP 1 REVERSE(value) FROM STRING_SPLIT(REVERSE(fullPath), '/') AS fileName
PostgreSQL的等效功能:
SELECT reverse(split_part(reverse(column_name), '/', 1));
你也可以尝试这个
( SELECT TOP(1) value
FROM STRING_SPLIT(#string, '/')
ORDER BY CHARINDEX('/' + value + '/', '/' + #string+ '-') DESC)
SELECT SUBSTRING( attachment, LEN(attachment)
- CHARINDEX('/', REVERSE(attachment)) + 2, LEN(attachment) ) AS filename
FROM filestable;
select reverse(SPLIT_PART(reverse('Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx'), '/',1))