Do you want to learn another useful T-SQL command with me?
Well, today we will show how to split strings easily, so.....no more recursive left o copy statements usage!
The STRING_SPLIT function
The String_Split function is a table-valued function that splits a string into rows of substrings.
Obviously, you can choose the separator character to use.
Well first I have to tell you that this function is part of SQL Server since version 13.00 (SQL 2016).but we are talking about it today because it has been significantly improved.
Using SQL Server 2022 we have a new parameter enable_ordinal:
If this parameter is set to 1 this function will return also a new column ordinal.
For example i can write the following command:
SELECT value, ordinal FROM STRING_SPLIT( 'My name is luke', ' ', 1)
I get four rows, a row for each word (the separator is the second parameter).
I can also order my rows by the field value or by the field ordinal.
I can easily extract for example the third "word" by ADDING a where to the SELECT.
SELECT value, ordinal FROM STRING_SPLIT( 'My name is luke', ' ', 1) WHERE ordinal = 3
That's all for today! ...And see you at the next post
Luke
