In MySQL, string functions are used to manipulate and analyze string data. Here are some commonly used string functions, 

These functions provide flexibility and ease in handling various string operations in MySQL queries

1. `CONCAT()`

-Purpose: Combines two or more strings into one.

-Eg

  SELECT CONCAT(‘Hello’, ‘ ‘, ‘World’) AS Result;

  Result: ‘Hello World’

2. `SUBSTRING()` or `SUBSTR()`

-Purpose: Extracts a substring from a string.

Eg

  SELECT SUBSTRING(‘Hello World’, 1, 5) AS Result;

  -Result: ‘Hello’

3. `LENGTH()`

– Purpose: Returns the length of a string in bytes.

Eg

  SELECT LENGTH(‘Hello’) AS Result;

  – Result: 5

4. `LOWER()` and `UPPER()`

Purpose: Converts a string to lowercase or uppercase.

– Example:

  SELECT LOWER(‘HELLO’) AS Result;

  — Result: ‘hello’

  SELECT UPPER(‘hello’) AS Result;

  — Result: ‘HELLO’

5. `TRIM()`

Purpose: Removes leading and trailing spaces from a string.

  SELECT TRIM(‘  Hello  ‘) AS Result;

  — Result: ‘Hello’

6. `REPLACE()`

– **Purpose**: Replaces occurrences of a specified string with another string.

Eg

  SELECT REPLACE(‘Hello World’, ‘World’, ‘MySQL’) AS Result;

  — Result: ‘Hello MySQL’

7. `INSTR()`

-Purpose: Returns the position of the first occurrence of a substring.

Eg

  SELECT INSTR(‘Hello World’, ‘World’) AS Result;

  — Result: 7

8. `LPAD()` and `RPAD()`

– **Purpose**: Pads a string with another string to a specified length.

Eg:

  SELECT LPAD(‘Hello’, 10, ‘*’) AS Result;

  — Result: ‘*****Hello’

  SELECT RPAD(‘Hello’, 10, ‘*’) AS Result;

  — Result: ‘Hello*****’

9. `REVERSE()`

-Purpose: Reverses the characters in a string.

Eg

  SELECT REVERSE(‘Hello’) AS Result;

  — Result: ‘olleH’

10. `FORMAT()`

Purpose:-  Formats a number as a string with grouped thousands and optional decimal places.

-Example:

  SELECT FORMAT(1234567.89, 2) AS Result;

  — Result: ‘1,234,567.89’

MODULE XXD Case statement 

A ‘CASE’ statement in MySQL allows you to implement conditional logic within your SQL queries. It evaluates a series of conditions and returns a value when the first condition is met. If no conditions are met, it returns an optional default value. The ‘CASE’ statement can be used in ‘SELECT’, ‘WHERE’, ‘ORDER BY’, and other SQL clauses.

Syntax

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    ELSE default_result

END

Example 

SELECT

    employee_id,

    salary,

    CASE

        WHEN salary > 50000 THEN ‘High’

        WHEN salary BETWEEN 30000 AND 50000 THEN ‘Medium’

        ELSE ‘Low’

    END AS salary_range

FROM

    employees;

In this example, the query categorizes employee salaries into ‘High’, ‘Medium’, or ‘Low’ based on the salary amount. The **CASE** statement is evaluated for each row, and the first condition that is true determines the output for the **salary_range** column.