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.
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 nth 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 nthstring 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 nthstring 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.
Join the conversation