The STRING_SPLIT function is a built-in function that was introduced in SQL Server 2016. It
splits a string into multiple substrings based on a specified separator.
The STRING_ESCAPE function escapes special characters in a string and returns the
STRING_SPLIT Function:
The STRING_SPLIT function takes two parameters: the string to be split and the separator
used to split the string. The function splits the string into substrings and returns them in a
table. The following is the syntax for the STRING_SPLIT function:
STRING_SPLIT (string, separator) Where,
• String is the input string that will be split
• Separator is the character or character sequence that will be used to split the string
The following is an example of how to use the STRING_SPLIT function:
DECLARE @string nvarchar(50) = ‘one,two,three,four’;
— Split string on comma delimiter
SELECT value FROM STRING_SPLIT(@string, ‘,’);
The result of the query above will be a table with four rows and one column. Each row in the
table will contain one of the 02045996879 substrings from the original string. In this case, the original
string was split on a comma delimiter so each substring corresponds to a word in the
original string.
STRING_ESCAPE Function:
The STRING_ESCAPE function escapes special characters in a string and returns the
resulting string with escaped special characters. The following is the syntax for the
STRING_ESCAPE function:STRING_ESCAPE (string, type)
Where,
• Type is the type of escaping to be used. The options are JSON, XML, or URL.
The following is an example of how to use the STRING_ESCAPE function:
DECLARE @string nvarchar(50) = ‘<script>alert(“Hello!”);</script>’;
— Escape string for use in XML
SELECT STRING_ESCAPE (@string, ‘XML’);
The result of the query above will be a string with the special characters “<” and “>”
escaped so that they can be used in XML without causing errors.
URL Escaping:
When you are working with URLs, there are times when you need to escape special
characters. For example, if you were to include a space in a URL, it would need to be
encoded as %20. The theSTRING_ESCAPE function can be used to escape special
characters in a string for use in a URL. The following is an example of how to use the
STRING_ESCAPE function to escape a string for use in a URL:
DECLARE @string nvarchar(50) = ‘This is a string with spaces’;
— Escape string for use in a URL
SELECT STRING_ESCAPE (@string, ‘URL’);
The result of the query above will be a string with the special characters “%20” replacing
the spaces. This escaped string gulfintms can now be used in a URL without causing errors.JSON Escaping:
When you are working with JSON strings, there are times when you need to escape special
characters. For example, if you were to include a backslash in a JSON string, it would need
to be encoded as \. The STRING_ESCAPE function can be used to escape special characters
in a string for use in JSON. The following is an example of how to use the STRING_ESCAPE
function to escape a string for use in JSON:
DECLARE @string nvarchar(50) = ‘This is a string with backslashes’;
— Escape string for use in JSON
SELECT STRING_ESCAPE (@string, ‘JSON’);
The result of the query above will be a string with the special character “” escaped so that
it can be used in JSON without causing errors.
XML Escaping:
When you are working with XML strings, there are times when you need to escape special
characters. For example, if you were to include the character “&” in an XML string, it would
need to be encoded as &. The STRING_ESCAPE function can be used to escape special
characters in a string for use in XML. The following is an example of how to use the
STRING_ESCAPE function to escape a string for use in XML:
DECLARE @string nvarchar(50) = ‘This is a &string& with &characters& that need to be
escaped’;
— Escape string for use in XML
SELECT STRING_ESCAPE (@string, ‘XML’);
The result of the query above will be a string with the special characters “&” and “>”
escaped so that they can be used in XML without causing errors.
The STRING_ESCAPE function is a useful function for escaping special characters instrings. This function can be used to escape special characters for use in XML, JSON, or
URL strings.
Conclusion:
The STRING_ESCAPE function is a useful function for escaping special characters in
strings. This function can be used to escape special characters for use in XML, JSON, or
URL strings. This function can be used to escape special characters in a string and return
the resulting string with escaped special characters.
