Introduction

While working on enhancing our Jive Migration utilities, we found ourselves trying to select a specific string segment within JSON content stored in an Azure SQL migration database.  We needed to split a string and select the last string segment, something like a Last() method.  Unfortunately, this functionality isn’t built-in to SQL.

The Alternatives

The team first looked at reversing the string, finding the first index of the separating character, then reversing the string back to pull the last segment.  This approach worked but lacked some flexibility in selecting a specific string segment.

A more flexible alternative would be to use the STRING_SPLIT() and ROW_NUMBER() functions. First, we’ll look at splitting a string and selecting the first (aka “First()”) string segment.  The STRING_SPLIT() function returns a single-column table whose rows are the substrings.  Let’s start with the following string:

[email protected] NVARCHAR(100)='one two three four five six seven eight nine ten';

The following statement uses the SPLIT_STRING() function to split the string at every space:

SELECT value
  FROM STRING_SPLIT(@mystring,' ');

And returns the following results:

value
---------
one
two
three
four
five
six
seven
eight
nine
ten

Selecting the First String Segment

To get the first string segment, we can simply take the first row using the “top 1” SQL statement shown here:

SELECT TOP 1 value
  FROM STRING_SPLIT(@mystring,' ');

Which has the following result:

value
---------
one

Selecting the Last String Segment

Now that we’re able to select the first string segment of a split string, let’s move on to selecting the last (aka “Last()”) string segment.  Our first thought may be to sort the STRING_SPLIT() function results in descending order and select the first row.  Unfortunately, that won’t work because the result is an alphabetized list of our segmented string, shown here:

value
---------
two
three
ten
six
seven
one
nine
four
five
eight

Instead, we’ll need to add another value to each row so that we can sort the table by row number. We’ll create a new column called “n” to represent the row number.  We’ll use the ROW_NUMBER() function and the OVER clause to populate the column as shown below:

SELECT value, n = ROW_NUMBER() OVER(ORDERBY (SELECT1))
  FROM STRING_SPLIT(@mystring,' ');

Which has the following results:

value     n
--------- ---------
one       1
two       2
three     3
four      4
five      5
six       6
seven     7
eight     8
nine      9
ten       10

Now we can sort the table by descending row number and take the top 1 row to get our last string as shown here:

SELECT TOP 1 value, n = ROW_NUMBER() OVER(ORDER BY (SELECT1))
  FROM STRING_SPLIT(@mystring,' ')
  ORDER BY n DESC;

Which has the following result:

value     n
--------- ---------
ten       10

Selecting a Specific String Segment

Finally, there may be situations where you need to take a specific string segment from the original split string that is neither first nor last.  To take the n^th string segment, we’ll need to first take the top n rows.  Then we’ll need to reverse the row order and select the first row from the reversed table. This will result the selecting the n^thstring segment.  The following statement demonstrates this query:

SELECT TOP 1 value, n
FROM (
SELECT TOP 7 value, n = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM STRING_SPLIT(@mystring,' ')) as my list
ORDER BY n DESC

Which, in this example, results in selecting the 7thstring segment:

value     n
--------- ---------
seven     7

Conclusion

Although SQL doesn’t have built in functions for selecting the First(), Last() and n^thstring segment from a longer string, were able to recreate this functionality using the STRING_SPLIT() and ROW_NUMBER() functions along with the OVER clause. If you thought this post was helpful, check out our other posts in Application Development.

Rob Horton

Rob Horton is a Senior Consultant and the Sustainment Practice Lead at ThreeWill. His experience includes over 20 years leading software architecture, design and development focusing on support tools, automation and e-commerce for large corporations and his own small businesses.