String Functions

1. Overview

The string functions covered in this section perform operations on a string (char or varchar) input value and return a string or numeric value.

ASCII

ASCII (American Standard Code for Information Interchange) returns the ASCII code value of the leftmost character of a character expression.‌

Syntax

ASCII (character_expression) return_type int

Example

SELECT ASCII('A') SELECT ASCII(1)

CHAR

This function converts an int between 0 to 255 to a character value. Outside of this range, the CHAR function will return a NULL value.‌

Syntax

CHAR (integer_expression) return_type char(1)

Arguments

integer_expression

An integer from 0 through 255.

Return Types

char(1)

Example

SELECT CHAR(65)
SELECT CHAR(100)

CHARINDEX

This function searches for one character expression inside another character string. If found, the function will return the starting position of the first expression.‌

Syntax

CHARINDEX ( expressionToFind , expressionString [ , start_location ] )

Arguments

expressionToFind The expression that needs to be found in the ExpressionString

expressionString The string that contains the expression

start_Location Start location from where the search will start

If CHARINDEX does not find expressionToFind within expressionString, CHARINDEX will return 0.

Example 1

Returning the starting position of an expression‌

This example searches for a in the string value.

SELECT CHARINDEX('a', 'this is a beautiful day');

Example 2

Returning the starting position of an expression with an optional start location‌

This example searches for a in the string value starting from 15th position.

SELECT CHARINDEX('a', 'this is a beautiful day', 15);

CONCAT

The CONCAT function concatenates two or more string values one after the other. This function requires at least 2 strings and no more than 254 strings to concatenate.‌

Syntax

CONCAT ( string1, string2 [, stringN ] 

Arguments

string A string to concatenate to the other strings.

Return Types

string A string with all the concatenated strings.

Example

SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;

DIFFERENCE

This function returns an integer value measuring the difference between the SOUNDEX () values of two different character expressions.strings.‌

Syntax

DIFFERENCE ( string , string )

Arguments

string

An alphanumeric expression of character data. string can be a constant, variable, or column.‌

Return Types

int

Example

SELECT SOUNDEX('day'), SOUNDEX('monday'), DIFFERENCE('day', 'monday');

FORMAT

Returns a value formatted with the specified format. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.‌

Syntax

FORMAT ( value, format )

Arguments

value

Expression of a supported data type to format. For a list of valid types, see the table in the following Remarks section.‌

format

nvarchar format pattern.‌

The format argument must contain a valid .NET Framework format string, either as a standard format string (for example, "C" or "D"), or as a pattern of custom characters for dates and numeric values (for example, "MMMM DD, yyyy (dddd)").

Return Types

nvarchar or null‌

The length of the return value is determined by the format.‌

The following table lists the acceptable data types for the value argument together with their .NET Framework mapping equivalent types.

Example 1

FORMAT with strings‌

The following example shows formatting date values by specifying a custom format.

SELECT FORMAT( GETDATE(), 'dd/MM/yyyy') AS 'DateTime Format'

Example 2

FORMAT with numerics

The following example shows formatting numeric values by specifying a custom format.

SELECT FORMAT(123456789,'###-##-####') AS 'Numeric Format';

LEFT

Returns the left part of a character string with the specified number of characters.‌

Syntax

LEFT ( string , integer )

Arguments

string

Is an expression of character or binary data. It can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert string.‌

integer

Is a positive integer that specifies how many characters of the string will be returned.

Return Types

Returns a string ‌

Example

The following example returns the two leftmost characters from the string.

SELECT LEFT('abcdefghi, 2) FROM domain.table

LEN

Returns the number of characters of the specified string expression, excluding trailing spaces.‌

Syntax

LEN ( string )

Arguments

string

Is the string expression.‌

Return Types

bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.‌

Example

The following example selects the number of characters and the data in string abcde.

SELECT LEN('abcde')

LOWER

Returns a character expression after converting uppercase character data to lowercase.‌

Syntax

LOWER ( string )

Arguments

string

Is an expression of character or binary data.

Return Types

varchar or nvarchar‌

Example

The following example uses the LOWER function.

SELECT LOWER('ABCDE') AS LowerString

LTRIM

Returns a character expression after it removes leading blanks.‌

Syntax

LTRIM ( string )

Arguments

string

Is an expression of character or binary data.‌

Return Types

varchar or nvarchar‌

Example: Using LTRIM

The following example uses LTRIM to remove leading spaces from a string

SELECT LTRIM(' Remove trailing spaces.')

PATINDEX

Returns the starting position of the first occurrence of a pattern in a specified expression.‌

Syntax

PATINDEX ( '%pattern%' , expression )

Arguments

pattern

Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern.

expression

Is an expression, typically a column that is searched for the specified pattern. expression is of the string data type category.‌

Return Types

int or bigint

Example

The following example checks a short character string (this is a great day) for the starting location of the characters eat.

SELECT PATINDEX('%eat%', 'this is a great day')

REPLACE

Replaces all occurrences of a specified string value with another string value.‌

Syntax

REPLACE ( string , string_toBeReplaced , string_replacedBy )

Arguments

string

Is the string expression to be searched.‌

string_toBeReplaced

Is the string to be found in the string.

string_replacedBy

Is the replacement string.

Return Types

Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.‌

Example

The following example replaces the string cde in abcdefghi with xyz.

SELECT REPLACE('abcdefghicde','cde','xyz');
GO

REVERSE

Returns the reverse order of a string value.‌

Syntax

REVERSE ( string )

Arguments

string

It is an expression of a string or binary data type.

Return Types

varchar or nvarchar‌

Example

The following example returns the reverse of the sting.

SELECT SELECT reverse('123456789')

RIGHT

Returns the right part of a character string with the specified number of characters.‌

Syntax

RIGHT ( string , integer )

Arguments

string

Is an expression of character or binary data. ‌

integer

Is a positive integer that specifies how many characters of string will be returned.

Return Types

Returns varchar when character_expression is a non-Unicode character data type.‌

Returns nvarchar when character_expression is a Unicode character data type.‌

Example

Using RIGHT with a string

SELECT RIGHT('Good Day', 5)

RTRIM

Returns a character string after truncating all trailing spaces.‌

Syntax

RTRIM ( string )

Arguments

string

Is an expression of character data. ‌

Return Types

varchar or nvarchar‌

Example

The following example takes a string of characters that has spaces at the end of the sentence and returns the text without the spaces at the end of the sentence.

SELECT RTRIM('Removes trailing spaces. ');

SOUNDEX

Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.‌

Syntax

SOUNDEX ( string )

Arguments

string

Is an alphanumeric expression of character data. SOUNDEX converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken.

Return Types

varchar‌

Example

The following example shows the standard SOUNDEX values are returned for all consonants. Returning the SOUNDEX for Raul and Rahul returns the same SOUNDEX result because all vowels, the letter y, doubled letters, and the letter h, are not included.

SELECT SOUNDEX ('Raul'), SOUNDEX ('Rahul');

SPACE

Returns a string of repeated spaces.‌

Syntax

SPACE ( integer_expression )

Arguments

integer_expression

Is a positive integer that indicates the number of spaces.

Return Types

varchar‌

Example

The following example concatenates a comma, two spaces, and the first name of the person.

SELECT 'John' + ',' + SPACE(2) + 'Doe'

STR

Returns character data converted from numeric data. The character data is right-justified, with a specified length and decimal precision.‌

Syntax

STR ( float_expression [ , length [ , decimal ] ] )

Arguments

float_expression

Is an expression of approximate numeric (float) data type with a decimal point.‌

length

Is the total length. This includes decimal point, sign, digits, and spaces. The default is 10.‌

decimal

Is the number of places to the right of the decimal point. decimal must be less than or equal to 16. If decimal is more than 16 then the result is truncated to sixteen places to the right of the decimal point.‌

Return Types

varchar‌

Example

The following example converts an expression that is made up of five digits and a decimal point to a six-position character string. The fractional part of the number is rounded to one decimal place.

SELECT STR(345.67, 6, 1);
GO

‌STUFF

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.‌

Syntax

STUFF ( string , start , length , replaceWith )

Arguments

string

Is an expression of character data.

start

Is an integer value that specifies the location to start deletion and insertion.

length

Is an integer that specifies the number of characters to delete.

replaceWith

Is an expression of character data.

Return Types

Returns character data if string is one of the supported character data types. Returns binary data if string is one of the supported binary data types.‌

Example

The following example returns a character string created by deleting three characters from the first string, abcdef, starting at position 2, at b, and inserting the second string at the deletion point.

SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO

SUBSTRING

Returns part of a character, binary, text, or image expression in SQL Server.‌

Syntax

SUBSTRING ( expression , start , length )

Arguments

expression

Is a character, binary, text, ntext, or image expression.‌

start

Is an integer or bigint expression that specifies where the returned characters start.

length

Is a positive integer or bigint expression that specifies how many characters of the expression will be returned.

Return Types

Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types. The returned string is the same type as the specified expression with the exceptions shown in the table.S

Example‌

The following example shows how to return only a part of a character string.

SELECT SUBSTRING('Rahul', 1, 1) AS FirstCharOfName

UPPER

Returns a character expression with lowercase character data converted to uppercase.‌

Syntax

UPPER ( string )

Arguments

string

Is an expression of character data.

Return Types

varchar or nvarchar‌

Examples

The following example uses the UPPER function to return the name in uppercase.

SELECT UPPER('rahul')