First, I want to advise you to avoid doing things as much as possible like this in T-SQL. Use CLR, if you can. Or do it outside of your really expensive database server.
You know, rational choices.
Recently, I had an interesting client problem where they had a variety of string formats, and different potential start and end points to get a substring from.
Normally, I love computed columns for this, but there was no way to get exactly what they wanted without using a whole bunch of them. It was sort of annoying.
Because it was easier to get across in T-SQL, we ended up using a trigger.
Don’t look at me like that.
First, let’s load up a small temp table with some different string values.
CREATE TABLE #strings ( string varchar(4) ); INSERT #strings ( string ) SELECT '1234' UNION ALL SELECT '2341' UNION ALL SELECT '3412' UNION ALL SELECT '4123';
Now, let’s say that there was a need to figure out which number came first in the string. You can use the CHARINDEX function to do that, but it’s a little difficult to make it “dynamic”.
To make things easy, let’s create a helper table of search patterns.
CREATE TABLE #searches ( search varchar(1) ); INSERT #searches ( search ) SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '4';
Now we can use a little bit of apply and derived table magic to figure out which of our search values appear first in our strings.
SELECT s.string, x1.search, x1.search_position FROM #strings AS s CROSS APPLY ( SELECT TOP (1) x0.search, x0.search_position FROM ( SELECT s2.search, search_position = CHARINDEX(s2.search, s.string) FROM #searches AS s2 ) AS x0 ORDER BY x0.search_position ) AS x1;
The results look like this:
SELECT s.string, x1.search, x1.search_position, x1.search_length, sub = SUBSTRING ( x1.search, x1.search_position, x1.search_length ) FROM #strings AS s CROSS APPLY ( SELECT TOP (1) x0.search, x0.search_position, x0.search_length FROM ( SELECT s2.search, search_position = CHARINDEX(s2.search, s.string), search_length = LEN(s2.search) FROM #searches AS s2 ) AS x0 ORDER BY x0.search_position ) AS x1;
Now the results look like this:
This is an admittedly pretty rudimentary example, but it’s tough to make this complicated without making it overly so.
From here, you can use a second APPLY with CHARINDEX ordered descending to find an end position, and use more advanced SUBSTRING techniques to locate the text between two positions.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.