SQL String Functions: Misc String Functions

SQL provides several miscellaneous string functions that allow manipulation of string values in different ways. Here, we will discuss some of the widely used string functions in SQL.

Syntax

The syntax for the different misc string functions in SQL are as follows:

1. REPLACE Function

REPLACE (string_value, string_to_replace, replacement_string)

2. REVERSE Function

REVERSE (string_value)

3. CHARINDEX Function

CHARINDEX (substring_value, string_value [, start_location])

4. LEFT Function

LEFT (string_value, number_of_characters)

5. RIGHT Function

RIGHT (string_value, number_of_characters)

Example

1. REPLACE Function

SELECT REPLACE('Hello World', 'World', 'SQL');

2. REVERSE Function

SELECT REVERSE('Hello World');

3. CHARINDEX Function

SELECT CHARINDEX('o', 'Hello World', 5);

4. LEFT Function

SELECT LEFT('Hello World', 5);

5. RIGHT Function

SELECT RIGHT('Hello World', 5);

Output

The output for the above examples will be:

1. REPLACE Function

Hello SQL

2. REVERSE Function

dlroW olleH

3. CHARINDEX Function

8

4. LEFT Function

Hello

5. RIGHT Function

World

Explanation

1. REPLACE Function

The REPLACE function is used to replace a string with another string.

In the above example, the 'World' string is replaced with 'SQL' in the 'Hello World' string.

2. REVERSE Function

The REVERSE function is used to reverse a string.

In the above example, the 'Hello World' string is reversed to 'dlroW olleH'.

3. CHARINDEX Function

The CHARINDEX function is used to find the starting index of a given substring in a string.

In the above example, the first occurrence of 'o' after the 5th position in the 'Hello World' string is found. The index value of 8 is returned.

4. LEFT Function

The LEFT function is used to extract a specified number of characters from the left side of a string.

In the above example, the first 5 characters from the left side of the 'Hello World' string are extracted.

5. RIGHT Function

The RIGHT function is used to extract a specified number of characters from the right side of a string.

In the above example, the last 5 characters from the right side of the 'Hello World' string are extracted.

Use

Misc string functions are used in many scenarios where manipulation of string values is required. Some of the common scenarios where these functions can be used are:

• Replacing a substring in a string
• Finding the starting index a substring in a string
• Extracting a specified number of characters from either the left or the right side of a string
• Reversing a string

Important Points

• The REPLACE function is case-sensitive.
• The CHARINDEX function is case-insensitive.
• If the start_location is not specified in the CHARINDEX function, it starts from the beginning of the string.
• If the number_of_characters is greater than the length of the string passed to the LEFT or RIGHT function, the entire string is returned.
• The string passed to the REVERSE function cannot be of type IMAGE, NTEXT, or TEXT.

Summary

In this page, we discussed the different misc string functions available in SQL, such as REPLACE, REVERSE, CHARINDEX, LEFT, and RIGHT. We provided examples of their syntax, output, explanation of their usage, important points to keep in mind while using them, and where they can be used.