Updated July 6, 2023
Introduction of T-SQL String Functions
Almost all web/desktop application needs a persistence layer to store the necessary information. Most of the application leverages database management system which stores data in a structured manner (unlike Mongo DB) like MySQL, SQL Server, Sybase, Postgres, and other database systems developed by tech giants like Oracle, IBM, and Microsoft. All these RDBMS products utilize one common language, which is SQL (Structured Query Language). Later these vendors developed their database language by extending the capability of the SQL language. T-SQL String Functions is one such language developed by Microsoft.
Let’s get started
Now that you know what T-SQL is, let’s get started. Let’s look at some useful built-in SQL functions in T-SQL.
T-SQL String Functions
Let us discuss the T-SQL String Functions below,
1. ASCII
It returns the ASCII code of the leftmost character. The return type is Integer.
Syntax:
ASCII ( character expression )
Result:
2. CHAR
2. CHARThis function does the opposite of the ASCII function, i.e., it converts the ASCII value to the character value.
Syntax:
CHAR (integer expression)
Result:
Tip: CHAR(13) can be used in the query for introducing a new line character.
3. CHARINDEX
This T-SQL string function returns the position of a substring in a string.
Syntax:
CHARINDEX(expressionToFind, expressionToSearch [, start_location])Arguments:
- expressionToFind: required parameter which denotes the substring to search for
- expressionToSearch: required parameter, string in which the substring is to be searched
- start_location: optional parameter defines the position from which the search will start; if not defined, then it will start from the beginning of the string by default.
Result:
4. CONCAT
This function appends two or more string values in an end-to-end manner and returns the concatenated string.
Syntax:
CONCAT ( string_value1, string_value2 [, string_valueN ] )Arguments:
The concat function requires at least two string arguments that can extend up to 254 string arguments.
Return Type
The return type is a concatenated string of the input strings.
Result:
5. CONCAT_WS
This T-SQL string function appends two or more string values in an end-to-end manner and separates them with the delimiter defined in the first parameter.
Syntax:
CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )Arguments:
- Separator: it could be an expression of any type char, nchar, nvarchar, or varchar
Return Type
The return type concatenated the string with a delimiter in between the strings.
Result:
Note: In the case of Null values concat_ws does not append delimiters and returns an empty value.
Result:
6. Difference
This function returns an integer value measuring the SOUNDEX() values of the two character expressions.
Syntax:
DIFFERENCE ( character_expression , character_expression )Arguments:
- character_expression: it could be an alphanumeric expression of a character data; it can be a constant, variable, or column.
Return Type
Integer
Note: difference compares two different SOUNDEX values and returns an integer value; this value indicates the SOUNDEX values match in a range of 0 to 4, where 0 signifies no match or zero similarity and 4 signifies identically matching strings.
Result:
7. FORMAT
This T-SQL string function returns the formatted string value in the specified format and optional culture. Use this function for locale formatting of date, time, and number value.
Syntax:
FORMAT ( value, format [, culture ] )Arguments:
- Value: required, the value to be formatted.
- Format: required, defines the format pattern
- Culture: optional, defines a local culture
Result:
8. LEFT
This function returns the specified number of characters starting from the left.
Syntax:
LEFT ( character_expression , integer_expression )Arguments:
- character_expression: required, signifies the character string from which characters will be extracted
- integer_expression: required, signifies the number of characters to extract.
Result:
9. LEN
This T-SQL string function returns the number of characters of a specified string.
Syntax:
LEN ( string_expression )Arguments:
- string_expression: the string variable whose length is to be determined. It can be a constant, string variable, or column.
Result:
10. LOWER
This function returns the string expression after converting the uppercase characters to lowercase.
Syntax:
LOWER ( character_expression )Arguments:
- character_expression: signifies the string that needs to be converted in lowercase.
Result:
11. LTRIM
This T-SQL string function returns the character expression after removing the leading spaces.
Syntax:
LTRIM ( character_expression )Arguments:
- character_expression: required field, the string variable from which leading spaces are to be removed.
Result:
12. NCHAR
This function returns the Unicode character of the specified integer code defined by the Unicode standard.
Syntax:
NCHAR ( integer_expression )Arguments:
- integer_expression: It can be a positive integer value from 0 to 6535; if a value greater than this is specified, Null is returned.
Result:
13. PATINDEX
This T-SQL string function returns the index of the first occurrence of the specified pattern in a string expression.
Syntax:
PATINDEX ( '%pattern%' , expression )Arguments:
- Pattern: requires a field; this is a character expression that contains a sequence to be found in the string expression. Wildcard characters can also be used in the pattern; the maximum limit is 8000 characters. It must be surrounded by %.
- Expression: it can be a column in which the specified pattern is to be searched.
Result:
14. REPLACE
This function replaces all occurrences of a specified string value with another value.
Syntax:
REPLACE ( string_expression , string_pattern , string_replacement )Arguments:
- string_expression: required; this is the string expression to be searched.
- string_pattern: required; this is the string pattern in the String expression.
- string_replacement: required, this is the string that replaces the string pattern.
Result:
15. SUBSTRING
This function extracts a specified character from a string.
Syntax:
SUBSTRING ( expression ,start , length )Arguments:
- Expression: required, defines the string expression from which a part would be extracted.
- Start: required, defines the starting index from which the string will be extracted.
- Length: required, defines the length up to which the string must be extracted.
Result:
16. Reverse
This function reverses the string expression character by character and returns the reversed string.
Syntax:
REVERSE ( string_expression )Arguments:
- String_expression: requires and defines the string which is to be reversed.
Result:
Conclusion
This tutorial taught us the commonly used and extremely useful string functions. These string functions are widely used while writing complex stored procedures and triggers. You can also create custom string functions using the predefined functions. That will be covered in the later part of the tutorial.
Recommended Articles
We hope that this EDUCBA information on “T-SQL String Functions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
