How to Extract the Last Element from a Delimited String Using SQL?

Extracting the Last Element from a Split String

In situations where you have a table containing strings with multiple components separated by delimiters (such as slashes in this example), the task of extracting the last element of each component becomes essential. This can be achieved through various programming techniques.

Utilizing SQL, one effective approach is to employ the CHARINDEX and SUBSTRING functions. The CHARINDEX function locates the position of a specified substring within a string, allowing you to identify the delimiter's presence. The SUBSTRING function then extracts the desired portion from the string.

For instance, if you have a table with the following values:

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

And you wish to extract the last element (the filenames):

Diet.aspx
MeettheFoodieandtheMD.aspx
OurMenu.aspx

You can use the following SQL statement:

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

In this statement:

• LEN(string): Returns the length of the string.
• CHARINDEX('/', REVERSE(string)): Searches for the last occurrence of the delimiter '/' in the reversed string. This determines the starting position of the last element.
• SUBSTRING(string, ..., LEN(string)): Extracts the last element from the string, starting from the position identified by CHARINDEX.

By executing this query, you will obtain the desired results, isolating the last elements of the input strings.

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