SQL Server String Functions

A string refers to a sequence of characters in a literal value or stored as a variable in programming. Strings are a fundamental building block in development, and almost any language supports it as a data type.

In SQL, you will work with string values more times than not. Therefore, it is to learn a few functions on how to manipulate and work on string values.

In this article, we will explore string functions provided by the SQL Server and understand how to use them.

What is a String Function?

A string function refers to taking a string as input and returning a string or numerical value depending on the specified operation.

The following are some of the most common and useful string functions in SQL Server.

Upper & Lower Functions

The upper() and lower() functions allow you to convert a specified string to the respective character casing. The upper() function will convert the specified string to uppercase while the lower() functions convert the string to lowercase.

An example is as shown:

SELECT UPPER('linuxhint')
SELECT LOWER('LINUXHINT');

The example queries returns the output as shown:

---------
LINUXHINT
---------
linuxhint

Concat Function

The concat() function allows you to concatenate two specified string values. An example is as shown:

SELECT concat('linux', 'hint');

The query should return output as shown:

---------
linuxhint
(1 ROW affected)

Len Function

The len function returns the number of characters in a specified string. Example:

SELECT len('linuxhint');

The function returns the number of characters as an integer as:

-----------
9
(1 ROW affected)

Reverse Function

The reverse function returns a specified string in the reverse order. For example:

SELECT reverse('linuxhint');

Output is as shown:

---------
tnihxunil
(1 ROW affected)

Ascii Function

The ascii function in SQL Server returns the ASCII code for the first character in the specified string.

SELECT ascii('linuxhint'), ascii('x');

The return values are as:

----------- -----------
108         120
(1 ROW affected)

Char Function

This function accepts an ascii value and returns its character representation. For example:

SELECT CHAR(120);

Output:

----
x
(1 ROW affected)

Substring Function

The substring() function extracts a substring of the specified length from the set starting point. The syntax is as:

SUBSTRING('string', start_location, substring_length);

An example usage is as shown:

SELECT SUBSTRING('Welcome to linuxhint', 12, 9);

Note the start location is 1 based. The function returns the substring as:

---------
linuxhint
(1 ROW affected)

Trim Function

The trim function removes all leading and trailing space characters from a specified string. An example usage is as shown:

SELECT TRIM('   SQL Server string functions  ');

The above query returns the string above with the leading and trailing spaced removed.

--------------------------------
SQL Server string functions
(1 ROW affected)

Charindex Function

The charindex function returns the index position of a defined string from the specified search location. Similarly, the index is 1 based.

Example usage:

SELECT charindex('functions', 'SQL Server string functions  ');

The function returns the starting index matching the specified string as:

-----------
19
(1 ROW affected)

String_split Function

The string_split in SQL Server converts a set of strings into rows based on a defined separator. An example usage is as shown:

SELECT VALUE FROM string_split('MongoDB,MySQL,PostgreSQL', ',');

The function returns the rows of the strings separated by a comma as shown:

VALUE
--------------------------
MongoDB
MySQL
PostgreSQL
(3 ROWS affected)

Replicate Function

This function returns a specified string repeated a set number of times. For example, to repeat a string 5 times, we can do:

SELECT replicate('linuxhint ', 5);

We should get an output as:

--------------------------------------------------
linuxhint linuxhint linuxhint linuxhint linuxhint
(1 ROW affected)

Left & Right Functions

The left and right functions extract a number of characters from a string starting from left and right, respectively.

Consider the examples below:

SELECT LEFT('linuxhint ', 5);
SELECT RIGHT('linuxhint', 4);

• The first example will extract 5 characters from the string in the left direction.
• The second example extracts 4 characters on the right side.

The resulting values are as shown:

-----
linux
----
hint

Soundex Function

The soundex function returns a four-character code based on how the specified string is spoken.

For example:

SELECT soundex('linux');
SELECT soundex('linus');

The return values is as:

-----
L520
L520

Both the defined string sound similar; the return values are the same as shown above.

Space Function

The space function returns a set of repeated spaces. An example is as shown:

SELECT 'This' + SPACE(1) + ' is linuxhint'

The query above returns:

------------------
This  IS linuxhint

Wrap Up

In this article, we covered popular and useful string functions in SQL Server. These functions allow you to manipulate and perform various operations on string data types in SQL Server. Check out the SQL Server functions in the documentation.

from https://ift.tt/3GBKWBK