Blog

CONCAT(), LENGTH(), UPPER(), LOWER(), SUBSTRING()

In SQL database management systems, string functions help database users manipulate and control character string data in various ways. Here are some of the most commonly used string functions, such as CONCAT(), LENGTH(), UPPER(), LOWER(), and SUBSTRING() string functions. String functions help in adding string text content values, modifying existing strings, or extracting individual parts of string text.

CONCAT() SQL String Function.

The CONCAT() function in SQL database management systems is used to combine two or more text strings into a single string. You can add and display multiple separate text strings together.

CONCAT() String Function Syntax.

CONCAT(string1, string2, …, stringN)

Elements of CONCAT() SQL String Function

• string1, string2, …, stringN – Here, string1, string2, etc., are the strings or text columns that the database user wants to combine.

Example of CONCAT() String Function.

For example, here we combine and display the employee name and department from the employee table.

SELECT CONCAT(emp_name, ‘ ‘, department ) AS full_detail

FROM employe;

Result of CONCAT() String Function.

full_detail

Siddhi Deora Marketing

Harry Deora Development

In this example.

• In this example, the CONCAT() function combines the emp_name and department columns from the employee table, adding a space between them, to create and display the employee’s full details.

LENGTH() SQL String Function.

The LENGTH() function in SQL database management systems returns the number of characters in a given text string.

LENGTH() String Function Syntax.

LENGTH(string)

Elements of LENGTH() String Function.

• string – This is the existing string whose length the user wants to count or measure.

Example of the LENGTH() String function.

For example, to get the length of each employee’s name in the employee table of the database.

SELECT emp_name, LENGTH(emp_name) AS name_length

FROM employe;

Result of the LENGTH() String function.

emp_name        name_length

Siddhi Deora     11

Harry Deora      11

In this example.

• Here, the LENGTH() function returns the number of characters in the emp_name of each employee in the employee table.

UPPER() SQL String Function.

In the SQL database management system, the UPPER() function converts all characters of a string provided by the user to uppercase.

Syntax of the UPPER() SQL String Function.

UPPER(string)

Elements of the UPPER() SQL String Function.

• string – This is the string that the database user wants to convert to uppercase.

Example of the UPPER() SQL String Function.

Here, to display the employee names in uppercase in the employee table.

SELECT emp_name, UPPER(emp_name) AS upper_emp_name

FROM employe;

Result of the UPPER() SQL String Function.

emp_name        upper_emp_name

SIDDHI     DEORA

HARRY     DEORA

In this example.

• Here, the UPPER() function converts the values ​​of the emp_name column in the existing employee table to uppercase.

LOWER() SQL String Function.

In the SQL database management system table, the LOWER() function converts all the text or characters of a string to lowercase.

Syntax of the LOWER() SQL String Function.

LOWER(string)

LOWER() SQL String Function element.

• string – This is the string in the existing table that you want to convert to lowercase.

Example of the LOWER() SQL String Function.

Here, we will use this code to display the employee names in lowercase in the Employee table.

SELECT emp_name, LOWER(emp_name) AS lower_emp_name

FROM employe;

Result of the LOWER() SQL String Function.

emp_name,       lower_emp_name

siddhi             deora

harry              deora

In this example.

• the LOWER() function in the Employee table converts the values ​​of the lower_emp_name column to lowercase.

SUBSTRING() SQL String Function.

The SUBSTRING() function in SQL database management systems is used to extract a portion of an existing string, starting from a particular point, and optionally extracting text or a string up to a specific length.

Syntax of the SUBSTRING() String Function.

SUBSTRING(string, start_position, length)

Elements of the SUBSTRING() String Function.

• string – This is the string from which the user wants to extract a part.
• start_position – This is the position in the text string (1-based index) where the string extraction starts.
• length – This is an (optional) numeric value for the number of characters to extract from the string. If omitted, it extracts all characters from the start_position to the end of the string.

Example of the SUBSTRING() String Function.

Here, we are extracting the first 4 characters from the emp_name column in the employee table.

SELECT emp_name, SUBSTRING(emp_name, 1, 4) AS first_four_character

FROM employe;

Result of the SUBSTRING() SQL String Function.

emp_name          first_four_character

Siddhi Deora        Sidd

Harry Deora         Harr

In this example.

• In this example, the SUBSTRING() function extracts the first 4 characters from the emp_name column of the employee table.

Another Example of the SUBSTRING() String Function.

Here, we are extracting the string text from a specific string text position to the end of the string.

In this example, we are extracting all characters starting from the 2nd position of the department column.

SELECT department, SUBSTRING(department, 2) AS department_from_second_char

FROM employe;

In this example.

• In this example, the SUBSTRING() string function starts from the 2nd character of the string and extracts and displays the rest of the string.

Detail explanation of string Functions

Conclusion of CONCAT(), LENGTH(), UPPER(), LOWER(), SUBSTRING() String Functions.

String functions (CONCAT(), LENGTH(), UPPER(), LOWER(), and SUBSTRING()) in SQL database management systems are essential for working with string data and converting string text content in SQL databases.

Popular String Functions and their Behavior.

• Use the (CONCAT()) string function to combine two different string texts.
• Use the (LENGTH()) string function to count the length of a string provided by the user.
• Use the (UPPER(), LOWER()) string functions to convert the character string to uppercase or lowercase.
• Use the (SUBSTRING()) string function to extract a particular part of a large text or string.