Session24 - SQL Server - SQL Functions | Upper | Lower | Left | Right | Trim | Substring | CharInde

SQL functions are built-in operations that allow you to manipulate and transform data in the database. Here's an explanation of some common SQL functions:

1. UPPER(): This function is used to convert a string to uppercase letters. It takes a string as input and returns the same string with all characters converted to uppercase.

Example:
SELECT UPPER('hello world') AS UppercaseString; -- Output: "HELLO WORLD"

2. LOWER(): This function is used to convert a string to lowercase letters. It takes a string as input and returns the same string with all characters converted to lowercase.

Example:
SELECT LOWER('Hello World') AS LowercaseString; -- Output: "hello world"

3. LEFT(): This function is used to extract a specified number of characters from the left side of a string. It takes two arguments - the input string and the number of characters to extract.

Example:
SELECT LEFT('Hello World', 5) AS LeftString; -- Output: "Hello"

4. RIGHT(): This function is used to extract a specified number of characters from the right side of a string. It takes two arguments - the input string and the number of characters to extract.

Example:
SELECT RIGHT('Hello World', 5) AS RightString; -- Output: "World"

5. TRIM(): This function is used to remove leading and trailing spaces (or other specified characters) from a string. By default, it removes spaces.

Example:
SELECT TRIM(' Hello World ') AS TrimmedString; -- Output: "Hello World"

6. SUBSTRING(): This function is used to extract a substring from a given string. It takes three arguments - the input string, the starting position (1-based index), and the number of characters to extract.

Example:
SELECT SUBSTRING('Hello World', 7, 5) AS SubstringResult; -- Output: "World"

7. CHARINDEX(): This function is used to find the starting position of a substring within a given string. It returns an integer value representing the position of the first occurrence of the substring.

Example:
SELECT CHARINDEX('o', 'Hello World') AS CharIndexResult; -- Output: 5 (since 'o' appears at the 5th position in "Hello World")

Note that the exact syntax and functionality of these functions may vary slightly depending on the specific SQL database system you are using (e.g., MySQL, PostgreSQL, SQL Server). The examples provided here are based on standard SQL syntax.