Transact-SQL (T-SQL) includes a number of scalar functions that allow us to perform operation on strings when working with SQL Server. These functions accept an input string, and return either a string or a numeric value.
The following is a list of T-SQL string functions available in SQL Server. Click on each function or operator name to see an explanation of the function, its syntax, and examples.
Continue readingIn SQL Server, the T-SQL TRANSLATE() function returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters specified in the third argument.
It’s similar to the REPLACE() function, but with some important differences.
The TRANSLATE() function requires 3 arguments; the input string, the characters to be replaced, and the characters to replace them.
In SQL Server, the T-SQL STRING_ESCAPE() function escapes special characters in texts and returns the text with escaped characters.
You provide the text and the escape type as arguments when calling the function.
In SQL Server, the T-SQL STR() function returns character data converted from numeric data. The return value has a return type of varchar.
You provide the numeric data as an argument when calling the function. The function then converts it to character data. There are also two optional arguments that you can use to specify the length, and the number of places to the right of the decimal point.
In SQL Server, you can use the T-SQL SPACE() function to generate a specific number of spaces.
This can be handy for adding spaces within a string, for example, when concatenating two or more strings.
The way it works is, you provide the number of spaces you need, and it will return a string of exactly that number of spaces.
In SQL Server, the T-SQL REPLICATE() function repeats a string value a specified number of times and returns the result.
The function accepts two arguments; the input string, and the number of times it should be repeated.
In SQL Server, the T-SQL QUOTENAME() function returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.
It was designed for quoting databases and their objects.
The function accepts two arguments; the input string (required), and a delimiter character (optional).
In SQL Server, you can use the T-SQL PATINDEX() function to find a pattern within a string. Specifically, the function only returns the first occurrence of the pattern within the string.
The function accepts two arguments; the pattern, and the string.
You can use wildcard characters within the pattern.
In SQL Server, you can use the T-SQL NCHAR() function to return the Unicode character based on the specified integer code.
You provide the integer code as an argument, and the function will return the Unicode character as defined by the Unicode standard.
In SQL Server, you can use the T-SQL RIGHT() function to return a given number of characters from the right part of a string.
The function accepts two arguments; the string itself, and the number of characters you’d like to be returned from that string.