MySQL

In MySQL, string functions allow you to manipulate and query string data effectively. These functions help with tasks like extracting substrings, changing case, replacing characters, and combining multiple strings. Understanding string functions is essential for working with text-based data in MySQL, especially when dealing with names, addresses, or other string fields in your tables.

Here’s a guide to some of the most common MySQL string functions, along with examples:

1. CONCAT():

   • The CONCAT() function is used to concatenate two or more strings into a single string.
   • Syntax:

   SELECT CONCAT(first_name, ' ', last_name) AS full_name 
   FROM employees;

   • This query combines the first_name and last_name columns to display the full name of employees.

2. SUBSTRING():

   • The SUBSTRING() function extracts a portion of a string starting from a given position.
   • Example:

   SELECT SUBSTRING(first_name, 1, 3) AS name_prefix 
   FROM employees;

   • This query extracts the first three characters of the employee's first name.

3. LENGTH():

   • The LENGTH() function returns the length of a string in bytes (or characters).
   • Example:

   SELECT first_name, LENGTH(first_name) AS name_length 
   FROM employees;

   • This query retrieves the length of each employee’s first name.

4. LOWER() and UPPER():

   • The LOWER() function converts a string to lowercase, while UPPER() converts it to uppercase.
   • Example:

   SELECT UPPER(first_name) AS upper_name, LOWER(last_name) AS lower_name 
   FROM employees;

   • This query returns the first name in uppercase and the last name in lowercase.

5. TRIM():

   • The TRIM() function removes leading and trailing spaces (or other specified characters) from a string.
   • Example:

   SELECT TRIM(first_name) AS trimmed_name 
   FROM employees;

   • This query removes any extra spaces from the beginning and end of the employee’s first name.

6. REPLACE():

   • The REPLACE() function replaces all occurrences of a substring within a string with a new substring.
   • Example:

   SELECT REPLACE(first_name, 'a', 'A') AS replaced_name 
   FROM employees;

   • This query replaces all occurrences of lowercase 'a' with uppercase 'A' in the first_name column.

7. INSTR():

   • The INSTR() function returns the position of the first occurrence of a substring within a string.
   • Example:

   SELECT INSTR(first_name, 'a') AS position_of_a 
   FROM employees;

   • This query finds the position of the letter 'a' in the employee’s first name.

8. LEFT() and RIGHT():

   • The LEFT() function extracts a specified number of characters from the left side of a string, while RIGHT() extracts characters from the right side.
   • Example:

   SELECT LEFT(first_name, 2) AS first_two_chars, RIGHT(last_name, 3) AS last_three_chars 
   FROM employees;

   • This query returns the first two characters of the first name and the last three characters of the last name.

9. LPAD() and RPAD():

   • The LPAD() and RPAD() functions pad a string on the left or right with a specified character up to a certain length.
   • Example:

   SELECT LPAD(employee_id, 5, '0') AS padded_id 
   FROM employees;

   • This query pads the employee ID with leading zeros to make it 5 characters long.

10. REVERSE():

    • The REVERSE() function reverses the characters in a string.
    • Example:

    SELECT REVERSE(first_name) AS reversed_name 
    FROM employees;

    • This query returns the employee’s first name with the characters in reverse order.

11. FORMAT():

    • The FORMAT() function formats a number as a string with a specified number of decimal places and adds commas for thousands.
    • Example:

    SELECT FORMAT(salary, 2) AS formatted_salary 
    FROM employees;

    • This query returns the salary formatted as a string with two decimal places.

12. ASCII() and CHAR():

    • The ASCII() function returns the ASCII value of the first character of a string, and the CHAR() function converts an ASCII code to a character.
    • Example:

    SELECT ASCII(first_name) AS ascii_value, CHAR(65) AS char_value;

    • This query returns the ASCII value of the first character of first_name and the character corresponding to ASCII code 65.

Using string functions in MySQL helps manipulate text data and customize query results for better reporting and data processing. These functions are essential tools for formatting, searching, and altering string data in a database.

To gain complete access, login with gmail or outlook, no need of signup, click here