Advanced T-SQL String Searching And Parsing Techniques In SQL Server

IndexOf

---

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.

CharindexOf

---

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';

ApplyOf

---

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:

SubstringOf

---

Now we can add in the LEN and SUBSTRING functions in order to parse out the part of the column we’re interested in.

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!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount 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.