STRING_SPLIT Function and STRING_ESCAPE Function in SQL Server 2016

Introduction

For years, SQL Server developers relied on complex User Defined Functions (UDFs), XML hacks, or recursive CTEs just to split a simple comma-separated string. SQL Server 2016 finally addressed this pain point by introducing two native system functions: STRING_SPLIT and STRING_ESCAPE.

These functions aren’t just about cleaner code; they are built for performance. In this article, we will explore how to implement these functions and, more importantly, how to solve the common limitations found in the 2016 implementation.

The Prerequisites: Compatibility Level 130

Before you begin, there is a common “gotcha.” Even if you are running SQL Server 2016, you might receive an “Invalid object name” error. This is because STRING_SPLIT requires your database to be at Compatibility Level 130 or higher.

To check and update your level, use the following:

-- Check current level
SELECT name, compatibility_level FROM sys.databases WHERE name = 'MyTechMantra';

-- Update to SQL Server 2016 level
ALTER DATABASE MyTechMantra SET COMPATIBILITY_LEVEL = 130;

STRING_SPLIT Function

The STRING_SPLIT function is a table-valued function (TVF) that breaks a string into a single-column table.

Syntax

STRING_SPLIT (string , separator)

Example

SELECT value FROM STRING_SPLIT('MyTechMantra Helps DBAs and Developers Daily', ' ');

Output of STRING_SPLIT Function in SQL Server 2016

MyTechMantra
Helps
DBAs
and
Developers
Daily

The Expert Challenge: The “Missing Ordinal” Problem

In the SQL Server 2016 version of STRING_SPLIT, the output rows are not guaranteed to be in the same order as the input string. Furthermore, there is no “index” column.

The Workaround: If order matters, you can use OPENJSON as a clever alternative in SQL Server 2016 to get an index (key):

DECLARE @jsonInput NVARCHAR(MAX) = '["MyTechMantra","Helps","DBAs"]';
SELECT [key] AS [Index], [value] AS [Word] FROM OPENJSON(@jsonInput);

STRING_ESCAPE Function

While STRING_SPLIT handles incoming data, STRING_ESCAPE is designed for outgoing data—specifically for JSON. It prevents malformed JSON by escaping special characters like backslashes, quotes, and control characters.

Example: Sanitizing for JSON

SELECT STRING_ESCAPE('C:\MyTechMantra\Reports"2026"', 'json') AS EscapedPath;

Output of STRING_ESCAPE Function in SQL Server 2016

C:\\MyTechMantra\\Reports\"2026\"

This is critical when building dynamic JSON strings to prevent “JSON Injection” or parsing errors in your front-end applications.

Performance Comparison: Native vs. XML

Why should you migrate your old code to STRING_SPLIT?

Conclusion

STRING_SPLIT and STRING_ESCAPE represent a major leap forward for T-SQL developers. By moving away from custom UDFs and XML parsing, you can significantly reduce the CPU overhead on your production servers.

Next Steps: Now that you’ve mastered string manipulation with native functions, join us for Part 9, where we explore the FORMATMESSAGE Statement. Learn how this enhancement simplifies custom message formatting and error handling in SQL Server 2016.

Read Next: Part 9: FORMATMESSAGE T-SQL Enhancement in SQL Server 2016

Frequently Asked Questions (FAQs)

1. Can STRING_SPLIT use a multi-character delimiter like ‘||’?

No, in SQL Server 2016, the separator must be a single character. For multi-character delimiters, you must still use the REPLACE workaround or a custom UDF.

2. Does STRING_SPLIT return a specific order?

No. The documentation explicitly states that the order is not guaranteed. If you need a specific order in 2016, consider the OPENJSON workaround.

3. What happens if the input string is NULL?

The function will return an empty table.

4. Can I use STRING_SPLIT in a JOIN?

Yes, it is most commonly used with CROSS APPLY to split values from a table column.

5. Does STRING_ESCAPE support XML escaping?

No, currently STRING_ESCAPE only supports the 'json' type.

6. Why am I getting “Invalid object name ‘STRING_SPLIT'”?

Check your database compatibility level. It must be 130 or higher.

7. Is STRING_SPLIT faster than XML splitting?

Yes, in most production benchmarks, the native C++ implementation of STRING_SPLIT outperforms XML-based splitting by 2x to 3x.

8. Can I filter the results of STRING_SPLIT?

Yes, since it returns a table, you can use a WHERE clause (e.g., WHERE value <> '' to remove empty entries).

9. Does STRING_ESCAPE handle emojis?

Yes, it correctly handles and escapes Unicode characters according to JSON standards.

SQL Server 2016 T-SQL Enhancement Series

• FORMATMESSAGE Statement
• SERVERPROPERTY Function
• TRUNCATE TABLE WITH PARTITIONS
• DROP IF EXISTS
• ALTER TABLE WITH (ONLINE = ON | OFF)
• MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE
• ALTER DATABASE SET AUTOGROW_ALL_FILES
• COMPRESS and DECOMPRESS Functions
• STRING_SPLIT and STRING_ESCAPE Functions

Trending SQL Server Articles and Tips