blocks|key|178995|text|在SQL中这样做的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|178996|SELECT+SUBSTRING(+string+,+LEN(string)+-++CHARINDEX('/',REVERSE(string))+%2B+2++,+LEN(string)++)+FROM+SAMPLE;|code-block|syntax|javascript|178997|JSFiddle此处http://sqlfiddle.com/#!3/41ead/11|offset|length|178998|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!3/41ead/11^0|0|0|A|X|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

The way to do it in SQL : 

<pre><code>SELECT SUBSTRING( string , LEN(string) - CHARINDEX('/',REVERSE(string)) + 2 , LEN(string) ) FROM SAMPLE;
</code></pre>

JSFiddle here <a href="http://sqlfiddle.com/#!3/41ead/11" rel="noreferrer">http://sqlfiddle.com/#!3/41ead/11</a>

blocks|key|396836|text|请尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|396837|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|code-block|syntax|javascript|396838|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Please try:

<pre><code>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
</code></pre>

blocks|key|396970|text|SELECT+REVERSE+((
++++SELECT+TOP+1+value+FROM+STRING_SPLIT(REVERSE('Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx'),+'/')
))+AS+fName
Result:+Diet.aspx|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|396971|标准STRING_SPLIT不允许取最后一个值。|unstyled|offset|length|style|CODE|396972|诀窍是在使用STRING_SPLIT拆分之前颠倒字符串(REVERSE)，从末尾获得第一个值(TOP+1+value)，然后需要再次颠倒结果(REVERSE)以恢复原始字符序列。|396973|以下是处理SQL表时的常用方法：|396974|SELECT+REVERSE+((
++++SELECT+TOP+1+VALUE+FROM+STRING_SPLIT(REVERSE(mySearchString),+'/')
))+AS+myLastValue
FROM+myTable+|396975|entityMap^0|0|2|C|0|6|C|S|7|1B|B|1Z|7|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|T|8|@$G|U|H|V|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@$G|X|H|Y|I|J]|$G|Z|H|10|I|J]|$G|11|H|12|I|J]|$G|13|H|14|I|J]]|9|@]|A|$]]|$1|M|3|N|5|F|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$B|C]]|$1|Q|3|-4|5|F|7|17|8|@]|9|@]|A|$]]]|R|$]]

<pre class="lang-sql prettyprint-override"><code>SELECT REVERSE ((
 SELECT TOP 1 value FROM STRING_SPLIT(REVERSE('Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx'), '/')
)) AS fName
Result: Diet.aspx
</code></pre>
Standard <code>STRING_SPLIT</code> does not allow to take last value.
The trick is to reverse the string (<code>REVERSE</code>) before splitting with <code>STRING_SPLIT</code>, get the first value from the end (<code>TOP 1 value</code>) and then the result needs to be reversed again (<code>REVERSE</code>) to restore the original chars sequence.
Here is the common approach, when working with SQL table:
<pre class="lang-sql prettyprint-override"><code>SELECT REVERSE ((
 SELECT TOP 1 VALUE FROM STRING_SPLIT(REVERSE(mySearchString), '/')
)) AS myLastValue
FROM myTable 
</code></pre>

blocks|key|396910|text|尝尝这个。这样更容易。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|396911|SELECT+RIGHT(string,+CHARINDEX('/',+REVERSE(string))+-1)+FROM+TableName|code-block|syntax|javascript|396912|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this. It's easier.
<pre><code>SELECT RIGHT(string, CHARINDEX('/', REVERSE(string)) -1) FROM TableName
</code></pre>

blocks|key|179120|text|一种稍微简洁一点的方法(类似于ktaria的答案，但在SQL+Server中)是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|179121|SELECT+TOP+1+REVERSE(value)+FROM+STRING_SPLIT(REVERSE(fullPath),+'/')+AS+fileName|code-block|syntax|javascript|179122|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

A slightly more compact way of doing this (similar to ktaria's answer but in SQL Server) would be
<pre><code>SELECT TOP 1 REVERSE(value) FROM STRING_SPLIT(REVERSE(fullPath), '/') AS fileName
</code></pre>

blocks|key|179097|text|更简单、更优雅：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|179098|select+reverse(SPLIT_PART(reverse('Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx'),+'/',1))|code-block|syntax|javascript|179099|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

more simple and elegant : 

<pre><code>select reverse(SPLIT_PART(reverse('Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx'), '/',1))
</code></pre>

blocks|key|396899|text|请尝试下面的代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|396900|SELECT+SUBSTRING(+attachment,+LEN(attachment)+
+++++-+CHARINDEX('/',+REVERSE(attachment))+%2B+2,+LEN(attachment)++)+AS+filename+
FROM+filestable;|code-block|syntax|javascript|396901|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Please try the code below: 

<pre><code>SELECT SUBSTRING( attachment, LEN(attachment) 
 - CHARINDEX('/', REVERSE(attachment)) + 2, LEN(attachment) ) AS filename 
FROM filestable;
</code></pre>

blocks|key|396998|text|你也可以试试这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|396999|(+SELECT+TOP(1)+value+
++++FROM+STRING_SPLIT(#string,+'/')+
ORDER+BY+CHARINDEX('/'+%2B+value+%2B+'/',+'/'+%2B+#string%2B+'-')+DESC)|code-block|syntax|javascript|397000|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can try this too
<pre><code>( SELECT TOP(1) value 
 FROM STRING_SPLIT(#string, '/') 
ORDER BY CHARINDEX('/' + value + '/', '/' + #string+ '-') DESC)
</code></pre>

blocks|key|179039|text|我针对单字符标记(D)和单字符标记(Diet.aspx)修正了jazzytomato的解决方案。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|179040|SELECT+SUBSTRING(+string+,+LEN(string)+-++CHARINDEX('/','/'%2BREVERSE(string))+%2B+2++,+LEN(string)++)+FROM+SAMPLE;|code-block|syntax|javascript|179041|entityMap^0|9|1|I|9|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

I corrected jazzytomato's solution for single character tokens (<code>D</code>) and for single tokens (<code>Diet.aspx</code>)

<pre><code>SELECT SUBSTRING( string , LEN(string) - CHARINDEX('/','/'+REVERSE(string)) + 2 , LEN(string) ) FROM SAMPLE;
</code></pre>

blocks|key|179076|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|179077|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|179127|text|PostgreSQL的等价物：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|179128|SELECT+reverse(split_part(reverse(column_name),+'/',+1));|code-block|syntax|javascript|179129|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The equivalent for PostgreSQL:
<pre class="lang-sql prettyprint-override"><code>SELECT reverse(split_part(reverse(column_name), '/', 1));
</code></pre>

blocks|key|396919|text|reverse(SUBSTRING(reverse(yourString),0,CHARINDEX('/',reverse(yourString))))+as+stringLastPart|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|396920|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>reverse(SUBSTRING(reverse(yourString),0,CHARINDEX('/',reverse(yourString)))) as stringLastPart
</code></pre>

I have a table with this values:

<pre><code>Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx
OurStory/MeettheFoodieandtheMD.aspx
TheFood/OurMenu.aspx
</code></pre>

I want to get this

<pre><code>Diet.aspx
MeettheFoodieandtheMD.aspx
OurMenu.aspx
</code></pre>

How can i do this?

Split string and take last element

SQL Server

PostgreSQL

我有一个表，里面有这个值：Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspxOurStory/MeettheFoodieandtheMD.aspxTheFood/OurMenu.aspx我想要得到这个Diet.aspxMeettheFoodieandtheMD.aspxOurMenu.aspx我该怎么做呢？

问拆分字符串并获取最后一个元素
EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问拆分字符串并获取最后一个元素EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问拆分字符串并获取最后一个元素
EN