T-SQL String Functions

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

This 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.

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:

1. T-SQL Commands
2. Introduction to MySQL
3. SQL Commands
4. Uses of SQL