SQL Server 2022 STRING_SPLIT Function - This function allows you to split a string by a delimiter and return a table of values.
The STRING_SPIT() function is a table-valued function to split the strings into substrings. The split is done based on the specified separator character. It is available for databases with compatibility level 130 (SQL Server 2016) or later.
The SQL Server 2022 enhances this table-valued function by adding an optional parameter – enable_ordinal.
Syntax:
STRING_SPLIT (string , separator [ , enable_ordinal ] )The enable_ordinal parameter is a flag to enable\disable the output column – original.
Value 1: Enable the ordinal column
Value 0 or NULL: Disable the original column
Let’s look at the SPLIT_STRING() function in SQL Server 2022.
SELECT * FROM STRING_SPLIT('Hi, My Name is Rajendra Gupta',' ', 1);
As shown below, it splits the strings and adds the column original with their respective string number.
With the value 0 or NULL for enable_ordinal, we do not get an ordinal column in the output.
SELECT * FROM STRING_SPLIT('Hi, My Name is Rajendra Gupta', ' ',0);
SELECT * FROM STRING_SPLIT('Hi, My Name is Rajendra Gupta', ' ',NULL);
What is the purpose of the new parameter – enable_ordinal. Well, we can filter a specific substring based on its ordinal value.
SELECT value, ordinal FROM STRING_SPLIT
('Hi, My Name is Rajendra Gupta',' ',1)WHERE ordinal = 5
Comments