How to Extract the Last Element of a String After the Final Slash in SQL?

Extracting the Last Element of a Split String

Problem:

Consider a table containing values of the form:

Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx
OurStory/MeettheFoodieandtheMD.aspx
TheFood/OurMenu.aspx

The objective is to extract the last element after the final "/" character from each string, such that the output becomes:

Diet.aspx
MeettheFoodieandtheMD.aspx
OurMenu.aspx

Solution:

SQL Approach:

To achieve this in SQL, utilize the following query:

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

• The SUBSTRING function takes three arguments: start position, length, and input string.
• The CHARINDEX function locates the first occurrence of a specified string within another string.
• REVERSE reverses the input string, allowing for the search for the final "/".
• This expression calculates the start position of the substring to extract (the end of the string minus the length from the last "/" to the end plus 2).
• The query selects the extracted substring from the specified SAMPLE table.

Example:

For the provided input, the output would be:

Diet.aspx
MeettheFoodieandtheMD.aspx
OurMenu.aspx

Refer to this JSFiddle for a live demonstration: http://sqlfiddle.com/#!3/41ead/11

The above is the detailed content of How to Extract the Last Element of a String After the Final Slash in SQL?. For more information, please follow other related articles on the PHP Chinese website!