String Functions - (Less Functions)

String functions are used to manipulate and format text strings in SQL queries. In this tutorial, we will discuss some of the commonly used string functions in SQL, specifically the "LESS FUNCTIONS" which include SUBSTR(), LENGTH(), CONCAT(), and REPLACE().

SUBSTR()

The SUBSTR() function is used to extract a substring from a text string. It takes three arguments:

• string: The text string to extract the substring from.
• start: The position to start extracting from within the string (index starts at 1).
• length (optional): The number of characters to extract.

Example

Let's extract a substring from the text string "Hello World":

SELECT SUBSTR('Hello World', 1, 5);

The output will be:

Hello

Explanation

In this example, we used the SUBSTR() function to extract a substring from the text string "Hello World". We started at the first position and extracted the first five characters of the string.

Use

The SUBSTR() function is commonly used to extract parts of a string, such as a first name or last name from a full name.

LENGTH()

The LENGTH() function is used to determine the length of a text string. It takes one argument:

• string: The text string to determine the length of.

Example

Let's determine the length of the text string "Hello World":

SELECT LENGTH('Hello World');

The output will be:

11

Explanation

In this example, we used the LENGTH() function to determine the length of the text string "Hello World".

Use

The LENGTH() function is commonly used to determine the length of a string before manipulating it with other string functions like SUBSTR() or REPLACE().

CONCAT()

The CONCAT() function is used to concatenate two or more text strings into a single string. It takes two or more arguments:

• string1, string2, ...: The text strings to concatenate.

Example

Let's concatenate the text strings "Hello" and "World":

SELECT CONCAT('Hello', ' ', 'World');

The output will be:

Hello World

Explanation

In this example, we used the CONCAT() function to concatenate the text strings "Hello" and "World", with a space between them.

Use

The CONCAT() function is commonly used to combine text strings together, such as combining a first name and last name into a full name.

REPLACE()

The REPLACE() function is used to replace a substring within a text string with another substring. It takes three arguments:

• string: The text string to replace a substring within.
• old_string: The substring to replace.
• new_string: The substring to replace old_string with.

Example

Let's replace the substring "Hello" with "Goodbye" in the text string "Hello World":

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

The output will be:

Goodbye World

Explanation

In this example, we used the REPLACE() function to replace the substring "Hello" with "Goodbye" within the text string "Hello World".

Use

The REPLACE() function is commonly used to replace parts of text strings, such as replacing a specific word or character within a larger string.

Summary

In this tutorial, we discussed four commonly used string functions in SQL: SUBSTR(), LENGTH(), CONCAT(), and REPLACE(). These functions can be used to manipulate and format text strings in SQL queries. Understanding these functions will allow you to work with text strings more efficiently in your SQL code.