Split string and take last element
SqlSql ServerUrlSplitSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
The way to do it in SQL :
SELECT SUBSTRING( string , LEN(string) - CHARINDEX('/',REVERSE(string)) + 2 , LEN(string) ) FROM SAMPLE;
JSFiddle here http://sqlfiddle.com/#!3/41ead/11
Solution 2 - Sql
SELECT REVERSE(LEFT(REVERSE(columnName), CHARINDEX('/', REVERSE(columnName)) - 1))
FROM tableName
ORHER SOURCE(s)
Solution 3 - Sql
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
Solution 4 - Sql
Try this. It's easier.
SELECT RIGHT(string, CHARINDEX('/', REVERSE(string)) -1) FROM TableName
Solution 5 - Sql
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.
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.
Here is the common approach, when working with SQL table:
SELECT REVERSE ((
SELECT TOP 1 VALUE FROM STRING_SPLIT(REVERSE(mySearchString), '/')
)) AS myLastValue
FROM myTable
Solution 6 - Sql
A slightly more compact way of doing this (similar to ktaria's answer but in SQL Server) would be
SELECT TOP 1 REVERSE(value) FROM STRING_SPLIT(REVERSE(fullPath), '/') AS fileName
Solution 7 - Sql
The equivalent for PostgreSQL:
SELECT reverse(split_part(reverse(column_name), '/', 1));
Solution 8 - Sql
Please try the code below:
SELECT SUBSTRING( attachment, LEN(attachment)
- CHARINDEX('/', REVERSE(attachment)) + 2, LEN(attachment) ) AS filename
FROM filestable;
Solution 9 - Sql
more simple and elegant :
select reverse(SPLIT_PART(reverse('Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx'), '/',1))
Solution 10 - Sql
You can try this too
( SELECT TOP(1) value
FROM STRING_SPLIT(#string, '/')
ORDER BY CHARINDEX('/' + value + '/', '/' + #string+ '-') DESC)
Solution 11 - Sql
I corrected jazzytomato's solution for single character tokens (D) and for single tokens (Diet.aspx)
SELECT SUBSTRING( string , LEN(string) - CHARINDEX('/','/'+REVERSE(string)) + 2 , LEN(string) ) FROM SAMPLE;
Solution 12 - Sql
reverse(SUBSTRING(reverse(yourString),0,CHARINDEX('/',reverse(yourString)))) as stringLastPart
Solution 13 - Sql
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
Solution 14 - Sql
The easiest way in MySQL:
SELECT SUBSTRING_INDEX(string, '/', -1) FROM SAMPLE;