Introduction of T-SQL String Functions
Almost all the 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 own 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 have got a gist of what T-SQL, let’s get started. Let’s have a look at some of the useful built-in SQL functions available in the 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 exact opposite of the ASCII function, i.e. it converts ASCII value to the character value.
Syntax:
CHAR ( integer expression )
Result:
Tip: CHAR(13) can be used in the query for introducing a newline character.
3.CHARINDEX
This T-SQL string function returns the position of a substring in a string.
4.5 (2,466 ratings)
View Course
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 by default it will start from the beginning of the string.
Result:
4.CONCAT
This function appends the 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 strings 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 the 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 a column.
Return Type
Integer
Note: difference compares two different SOUNDEX values and returns an integer value, this value is an indication of 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 extract
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 lower case.
Syntax:
LOWER ( character_expression )
Arguments:
character_expression: signifies the string that needs to be converted in the lower case.
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 then Null is returned.
Result:
13.PATINDEX
This T-SQL string function returns the index of the first occurrence of the specified pattern in a specified 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 to be found 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 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, defines the string which is to be reversed.
Result:
Conclusion
In this tutorial, we have learned the commonly used and extremely useful string functions. These string functions are commonly 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
This has been a guide to T-SQL String Functions. Here we have discussed the working of T-SQL String Functions with the help of Syntax. You may also look at the following articles to learn more:
