SQL Server String Functions

Main Article :- Sql Server ASCII() Function

The ASCII() method returns the character's ASCII value.

Syntax:

ASCII(character)

Parameter Values

Technical Details

Example

Return the first character in the text "Customer"' value:

SELECT ASCII('Customer')

Result: 67

Main Article :- Sql Server CHAR() Function

The ASCII character is returned by the CHAR() method.

Syntax

CHAR(code)

Parameter Values

Technical Details

Example

Return the following character using the numeric code 65:

SELECT CHAR(65) AS CodeToCharacter;

Result: A

Main Article :- Sql Server CHARINDEX() Function

The CHARINDEX() method returns the location of a substring found in a string.

This method returns 0 if the substring cannot be found.

Note that this function searches without regard for case.

Syntax

CHARINDEX(substring, string, start)

Parameter Values

Technical Details

Examples

Return position: Search for "OM" in the text "Customer."

SELECT CHARINDEX('OM', 'Customer') AS MatchPosition;

Result: 5

Return position (starting at position 3) after searching for "mer" in string "Customer":

SELECT CHARINDEX('mer', 'Customer', 3) AS MatchPosition;

Result: 6

Find the letter "t" in the string "Customer" and return the following position:

SELECT CHARINDEX('t', 'Customer') AS MatchPosition;

Result: 4

Main Article :- Sql Server CONCAT() Function

The CONCAT() function concatenates two or strings.

Note:See also CONCAT WS and Concat using the + operator ().

Syntax

CONCAT(string1, string2, ...., string_n)

Parameter Values

Technical Details

Examples

Add 3 strings together:

SELECT CONCAT('SQL', ' is', ' fun!');

Result: SQL is fun!

Strings are added together (each string is separated by a space character):

SELECT CONCAT('SQL', ' ', 'is', ' ', 'fun!');

Result: SQL is fun!

Add two strings together:

SELECT CONCAT('Simmanchith', '.com');

Result: simmanchith.com

You can combine two or strings with the + operator.

Note that the CONCAT() and CONCAT WS() methods are also available.

Syntax

string1 + string2 + string_n

Parameter Values

Technical Details

Examples

Add 3 strings together:

SELECT 'SQL' + ' is' + ' fun!';

Result: SQL is fun!

Strings are added together (each string is separated by a space character):

SELECT 'SQL' + ' ' + 'is' + ' ' + 'fun!';

Result: SQL is fun!

Add 2 strings together:

SELECT 'Simmanchith' + '.com';

Result: Simmanchith.com

CONCAT WS() is a function that joins two or strings with a separator.

Note that CONCAT() and CONCAT with the + operator are also available.

Syntax

CONCAT_WS(separator, string1, string2, ...., string_n)

Parameter Values

Technical Details

Examples

Combine the strings. To separate the concatenated string values, use '-':

SELECT CONCAT_WS('-', 'SQL', ' is', ' fun!');

Result: SQL- is- fun!

Combine the strings. To separate the concatenated string values, use '.':

SELECT CONCAT_WS('.', 'www', 'Simmanchith', 'com');

Result: www.Simmanchith.com

The number of bytes utilised to represent an expression is returned by the DATALENGTH() function.

When computing the length of an expression, the DATALENGTH() function counts both leading and following spaces.

Also check out the LEN() function.

Syntax

DATALENGTH(expression)

Parameter Values

Technical Details

Examples

Return the length of an expression in bytes (both leading and following spaces are counted):

SELECT DATALENGTH('   W3Schools.com   ');

Result:

Return the length (in bytes) of an expression:

SELECT DATALENGTH('2017-08');

Result: 7

Return the length (in bytes) of an expression:

SELECT DATALENGTH('W3Schools.com');

Result: 13

The DIFFERENCE() method produces an integer after comparing two SOUNDEX values. The integer number, ranging from 0 to 4, shows the match between the two SOUNDEX values.

0 means that the SOUNDEX values have little or no resemblance. 4 implies that the SOUNDEX values are very comparable or same.

Look at the SOUNDEX() method as well.

Syntax

DIFFERENCE(expression, expression)

Parameter Values

Technical Details

Examples

Returns an integer after comparing two SOUNDEX values:

SELECT DIFFERENCE('Juice', 'Banana');

Result: 2

Returns an integer after comparing two SOUNDEX values:

SELECT DIFFERENCE('Juice', 'Jucy');

Result: 4

Main Article :- Sql Server FORMAT() Function

The FORMAT() function applies the provided format to a value (and an optional culture in SQL Server 2017).

To format date/time and numeric data, use the FORMAT() function. Use CAST() or CONVERT for generic data type conversions ().

Syntax

FORMAT(value, format, culture)

Parameter Values

Technical Details

Examples

Format a number:

SELECT FORMAT(123456789, '##-##-#####');

Result: 12-34-56789

Format a date:

DECLARE @d DATETIME = '12/01/2018';
SELECT FORMAT (@d, 'd', 'en-US') AS 'US English Result',
               FORMAT (@d, 'd', 'no') AS 'Norwegian Result',
               FORMAT (@d, 'd', 'zu') AS 'Zulu Result';

Result:

Main Article :- Sql Server LEFT() Function

The LEFT() method removes a string's leftmost characters (starting from left).

Syntax

LEFT(string, number_of_chars)

Parameter Values

Technical Details

Examples

Take 100 characters from the following string (beginning at the left):

SELECT LEFT('SQL Tutorial', 100) AS ExtractString;

Result: SQL Tutorial

Extract the following three characters from a string (beginning at the left):

SELECT LEFT('SQL Tutorial', 3) AS ExtractString;

Result: SQL

Main Article :- Sql Server LEN() Function

The length of a string is returned by the LEN() method.

Note that the length does not include any trailing spaces at the end of the string. Leading spaces at the beginning of the string, on the other hand, are taken into account when computing the length.

Look at the DATALENGTH() method as well.

Syntax

LEN(string)

Parameter Values

Technical Details

Examples

Return the length of a string (leading but not trailing spaces are counted):

SELECT LEN(' simmanchith.com ');

Result: 16

Return the length of a string:

SELECT LEN('2017-08');

Result: 7

Return the length of a string:

SELECT LEN('hi');

Result: 2

Main Article :- Sql Server LOWER() Function

The LOWER() method lowers the case of a string.

Take a look at the UPPER() function as well.

Syntax

LOWER(text)

Parameter Values

Technical Details

Examples

Convert the text to lower-case:

SELECT LOWER('SQL Tutorial is FUN!');

Result: sql tutorial is fun!

The LTRIM() method trims a string's leading spaces.

Take a look at the RTRIM() method as well.

Syntax

LTRIM(string)

Parameter Values

Technical Details

Example

To remove leading spaces from a string, do the following:

SELECT LTRIM('     SQL Tutorial') AS LeftTrimmedString;

Result: SQL Tutorial

Based on the numeric code, the NCHAR() method returns the Unicode character.

Syntax

NCHAR(number_code)

Parameter Values

Technical Details

Example

Return the Unicode character that corresponds to the number 65:

SELECT NCHAR(65) AS NumberCodeToUnicode;

The PATINDEX() method returns the pattern's location inside a string.

This method returns 0 if the pattern is not detected.

Note that the string's initial position is 1 and the search is case-insensitive.

Syntax

PATINDEX(%pattern%, string)

Parameter Values

Technical Details

Examples

Return the pattern's position in a string:

SELECT PATINDEX('%s%com%', 'W3Schools.com');

Result: 9

Return the pattern's position in a string:

SELECT PATINDEX('%[ol]%', 'W3Schools.com');

Result: 6

Return the pattern's position in a string:

SELECT PATINDEX('%[z]%', 'W3Schools.com');

Result: 0

Return the pattern's position in a string:

SELECT PATINDEX('%schools%', 'W3Schools.com');

Result: 3

The QUOTENAME() method produces a Unicode string that has been delimited to make it a valid SQL Server delimited identifier.

Syntax

QUOTENAME(string, quote_char)

Parameter Values

Technical Details

Examples

Return a Unicode string delimited by parenthesis:

SELECT QUOTENAME('abcdef', '()');

Result: (abcdef)

Returns a Unicode string delimited by brackets (by default):

SELECT QUOTENAME('abcdef');

Result: [abcdef]

Main Article :- Sql Server REPLACE() Function

The REPLACE() method creates a new substring for every instances of a substring within a string.

It's worth noting that the search is case-insensitive.

Look at the STUFF() method as well.

Syntax

REPLACE(string, old_string, new_string)

Parameter Values

Technical Details

Examples

Replace "SQL" with "HTML":

SELECT REPLACE('SQL Tutorial', 'SQL', 'HTML');

Result: HTML Tutorial

Replace "a" with "c":

SELECT REPLACE('ABC ABC ABC', 'a', 'c');

Result: cBC cBC cBC

Replace "T" with "M":

SELECT REPLACE('SQL Tutorial', 'T', 'M');

Result: SQL MuMorial

The REPLICATE() function repeats a string a specified number of times.

Syntax

REPLICATE(string, integer)

Parameter Values

Technical Details

Examples

Repeat a string:

SELECT REPLICATE('SQL Tutorial ', 3);

Result: SQL Tutorial SQL Tutorial SQL Tutorial

Main Article :- Sql Server REVERSE() Function

The REVERSE() function reverses a string and returns the result.

Syntax

REVERSE(string)

Parameter Values

Technical Details

Examples

Reverse a string:

SELECT REVERSE('SQL Tutorial');

Result: lairotuT LQS

Main Article :- Sql Server RIGHT() Function

The RIGHT() method removes a string's characters one by one (starting from right).

Syntax

RIGHT(string, number_of_chars)

Parameter Values

Technical Details

Examples

Take 100 characters from the following string (beginning at the right):

SELECT RIGHT('SQL Tutorial', 100) AS ExtractString;

Result: SQL Tutorial

Extract the following three characters from a string (beginning at the right):

SELECT RIGHT('SQL Tutorial', 3) AS ExtractString;

Result: ial

The RTRIM() method eliminates spaces from the end of a string.

Take a look at the LTRIM() function as well.

Syntax

RTRIM(string)

Parameter Values

Technical Details

Example

Remove spaces at the end of a string:

SELECT RTRIM('SQL Tutorial     ') AS RightTrimmedString;

Result: SQL Tutorial

The SOUNDEX() method evaluates the similarity of two expressions by returning a four-character code.

The SOUNDEX() function turns a text to a four-character code based on how it sounds when spoken.

Check out the DIFFERENCE() method as well.

Syntax

SOUNDEX(expression)

Parameter Values

Technical Details

Examples

Return a four-character code based on the similarity of two strings:

SELECT SOUNDEX('Juice'), SOUNDEX('Banana');

Result: B550

Return a four-character code based on the similarity of two strings:

SELECT SOUNDEX('Juice'), SOUNDEX('Jucy');

Result: J200

SPACE() returns a string containing the requested amount of space characters.

SPACE(number)

Parameter Values

Technical Details

Example

Return a string with 10 spaces:

SELECT 'A' + SPACE(10) + 'B';

Result: A B

The STR() method returns a string representation of a number.

Syntax

STR(number, length, decimals)

Parameter Values

Technical Details

Examples

Return a number as a string:

SELECT STR(185.5);

Result: 186

Return a number as a string:

SELECT STR(185.476, 6, 2);

Result: 185.48

Return a number as a string:

SELECT STR(185);

Result: 185

The STUFF() method removes a portion of a string and then replaces it with another portion, starting at a given location.

Take a look at the REPLACE() method as well.

Syntax

STUFF(string, start, length, new_string)

Parameter Values

Technical Details

Examples

Starting at position 13, remove 1 character from a string and replace it with "is fun!":

SELECT STUFF('SQL Tutorial!', 13, 1, ' is fun!');

Result: SQL Tutorial is fun!

Remove three characters from a string starting at position 1 and replace them with "HTML":

SELECT STUFF('SQL Tutorial', 1, 3, 'HTML');

Result: HTML Tutorial

Main Article :- Sql Server SUBSTRING() Function

SUBSTRING() extracts a subset of characters from a string.

Syntax

SUBSTRING(string, start, length)

Parameter Values

Technical Details

Examples

Starting at point 1, extract 100 characters from a string:

SELECT SUBSTRING('SQL Tutorial', 1, 100) AS ExtractString;

Result: SQL Tutorial

Starting at position 1, extract three characters from a string:

SELECT SUBSTRING('SQL Tutorial', 1, 3) AS ExtractString;

Result: SQL

After the characters provided in the second argument are translated into the characters given in the third parameter, the TRANSLATE() method returns the string from the first argument.

If the lengths of the characters and translations disagree, the TRANSLATE() method will produce an error.

Syntax

TRANSLATE(string, characters, translations)

Parameter Values

Technical Details

Examples

Return the first argument's string. AFTER THE CHARACTERS IN THE SECOND ARITHMETIC ARE CONVERTED INTO THE CHARACTERS IN THE THIRD ARITHMETIC:

SELECT TRANSLATE('3*[2+1]/{8-4}', '[]{}', '()()'); // Results in 3*(2+1)/(8-4)

Result: 3*(2+1)/(8-4)

Return the first argument's string. AFTER THE CHARACTERS IN THE SECOND ARITHMETIC ARE CONVERTED INTO THE CHARACTERS IN THE THIRD ARITHMETIC:

SELECT TRANSLATE('Monday', 'Monday', 'Sunday'); // Results in Sunday

Result: Sunday

Main Article :- Sql Server TRIM() Function

The TRIM() method eliminates the space character from the beginning or end of a string, as well as other specified characters.

The TRIM() method, by default, eliminates leading and trailing spaces from a string.

Look into the LTRIM() and RTRIM() routines as well.

Syntax

TRIM([characters FROM ]string)

Parameter Values

Technical Details

Examples

To remove characters and spaces from a string, do the following:

SELECT TRIM('#! ' FROM '    #SQL Tutorial!    ') AS TrimmedString;

Result: SQL Tutorial

To remove leading and trailing spaces from a string, follow these steps:

SELECT TRIM('     SQL Tutorial!     ') AS TrimmedString;

Result: SQL Tutorial!

For the first character in the input expression, the UNICODE() method returns an integer value (the Unicode value).

Syntax

UNICODE(character_expression)

Parameter Values

Technical Details

Examples

For the first character in the input expression, return an integer value (a Unicode value):

SELECT UNICODE('Atlanta');

Result: 65

Main Article :- Sql Server UPPER() Function

The UPPER() method raises the case of a string.

Take a look at the LOWER() method as well.

Syntax

UPPER(text)

Parameter Values

Technical Details

Examples

Convert the text to upper-case:

SELECT UPPER('SQL Tutorial is FUN!');

Result: SQL TUTORIAL IS FUN!