SQL Server 2016 came out with the STRING_SPLIT function that will return what amounts to a table from character-separated string. Using a CROSS JOIN with this function, you can return one row multiple times with whatever values you want!

How Does It Work

If you review the STRING_SPLIT function, you will see that it will take any string with separators and return it as a table.

SELECT *
FROM string_split('A,B,C,D,E',',')

The results are as follows:

value
A
B
C
D
E

If we use a CROSS JOIN, we can have every combination of these values for each row.

The Nice Things About It

The first nice thing about this is that we can use whatever values we want.

SELECT TestId, ValTest, s.value
FROM TEST t
CROSS JOIN string_split('A,B,C,D,E',',') s

You get the following results:

TestId ValTest value
1 A A
1 A B
1 A C
1 A D
1 A E
2 B A
2 B B
2 B C
2 B D
2 B E

The second nice thing about this is that we can use it in our WHERE clause:

SELECT TestId, ValTest, s.value
FROM TEST t
CROSS JOIN string_split('A,B,C,D,E',',') s
WHERE s.value = t.ValTest

Results:

TestId ValTest value
1 A A
2 B B

The third nice thing about this is we can use it in our SELECT clause:

SELECT TestId, ValTest, s.value,
CASE
  WHEN s.value IN ('A','B') 
  THEN ValTest + s.value 
  ELSE s.value 
END SSandT
FROM TEST t
CROSS JOIN string_split('A,B,C,D,E',',') s
WHERE s.value != t.ValTest

Results:

TestId ValTest value SSandT
1 A B AB
1 A C C
1 A D D
1 A E E
2 B A BA
2 B C C
2 B D D
2 B E E

My Work Example

Today, I had to insert two records into a lineitems table. One line went to the invoice, the other line went to the claim. The way to tell them apart was the cfk_claims value. If it was 0, then it was for the invoice. The claim item would have the cfk_claims value populated. For that, I made my STRING_SPLIT table ‘0,1’. Then, I used a CASE statement, When my value was 0, the cfk_claims remained as 0. When my value was 1, the returned value was cfk_claims. Worked perfectly, and I can see how I could use this again in the future.

What do I think?

I think that STRING_SPLIT is pretty great! I can line up any values I want and have it appear however I want. In this instance, getting two records out and being able to use a CASE statement in such an easily-readable way was a “slick win” for me!

What do You Think?

I’m having fun with STRING_SPLIT. Can you think of some other uses for it?

References

Tagged: , , ,