SQL | String functions

SQL provides a myriad of functions to manipulate and manage string data. These functions can vary slightly across different database systems, but many are standard and ubiquitous. Here's a list of commonly used string functions:

CONCAT():
- Concatenates two or more strings together.
- SELECT CONCAT('Hello', ' ', 'World'); will return Hello World.
LENGTH() or LEN():
- Returns the length of the specified string.
- SELECT LENGTH('Hello'); will return 5.
UPPER():
- Converts a string to uppercase.
- SELECT UPPER('Hello'); will return HELLO.
LOWER():
- Converts a string to lowercase.
- SELECT LOWER('Hello'); will return hello.
TRIM():
- Removes leading and trailing spaces from a string.
- SELECT TRIM(' Hello '); will return Hello.
SUBSTRING() or SUBSTR():
- Extracts a portion of a string.
- SELECT SUBSTRING('Hello World', 1, 5); will return Hello.
REPLACE():
- Replaces all occurrences of a substring with another substring.
- SELECT REPLACE('Hello World', 'World', 'SQL'); will return Hello SQL.
CHARINDEX() or INSTR():
- Returns the position of the first occurrence of a substring in a string.
- SELECT CHARINDEX('lo', 'Hello'); will return 3.
LEFT():
- Returns the left part of a string with the specified number of characters.
- SELECT LEFT('Hello', 2); will return He.
RIGHT():

Returns the right part of a string with the specified number of characters.
SELECT RIGHT('Hello', 2); will return lo.

LTRIM():

Removes leading spaces from a string.
SELECT LTRIM(' Hello'); will return Hello.

RTRIM():

Removes trailing spaces from a string.
SELECT RTRIM('Hello '); will return Hello.

REVERSE():

Reverses a string.
SELECT REVERSE('Hello'); will return olleH.

PATINDEX():

Returns the position of the first occurrence of a pattern in a string.
SELECT PATINDEX('%lo%', 'Hello World'); will return 3.

These are just a few of the many string functions available in SQL. The availability and exact usage might differ slightly based on the RDBMS you are using, so always refer to the respective documentation for precise details.

How to use string functions in SQL:
- String functions in SQL are used for manipulating and processing character data.
```
SELECT UPPER('hello') AS upper_case, LOWER('WORLD') AS lower_case;
```
CONCAT() function in SQL for string concatenation:
- The CONCAT function is used to concatenate strings.
```
SELECT CONCAT('Hello', ' ', 'World') AS concatenated_string;
```
SUBSTRING() function in SQL:
- The SUBSTRING function extracts a portion of a string.
```
SELECT SUBSTRING('Hello World', 1, 5) AS extracted_string;
```
LENGTH() and LEN() functions in SQL for string length:
- The LENGTH (or LEN) function returns the length of a string.
```
SELECT LENGTH('Hello') AS string_length, LEN('World') AS another_string_length;
```
TRIM() and LTRIM() functions in SQL:
- The TRIM function removes leading and trailing spaces, while LTRIM removes leading spaces.
```
SELECT TRIM('   Hello   ') AS trimmed_string, LTRIM('   World') AS left_trimmed_string;
```
UPPER() and LOWER() functions in SQL:
- UPPER and LOWER functions convert a string to uppercase and lowercase, respectively.
```
SELECT UPPER('hello') AS upper_case, LOWER('WORLD') AS lower_case;
```
REPLACE() function in SQL for string replacement:
- The REPLACE function replaces occurrences of a substring with another substring.
```
SELECT REPLACE('Hello World', 'World', 'Universe') AS replaced_string;
```
SQL string functions for pattern matching:
- LIKE and other pattern matching functions are used for pattern-based comparisons.
```
SELECT column1
FROM example_table
WHERE column1 LIKE 'ABC%';
```
SPLIT() or SPLIT_PART() function in SQL:
- Some databases use SPLIT or SPLIT_PART to split a string into multiple parts based on a delimiter.
```
SELECT SPLIT_PART('apple,orange,banana', ',', 2) AS second_part;
```
SQL string functions for case-insensitive comparison:
- Some databases provide case-insensitive versions of string functions.
```
SELECT column1
FROM example_table
WHERE UPPER(column1) = 'SEARCHTERM';
```
INSTR() or CHARINDEX() function in SQL for substring position:
- INSTR (or CHARINDEX) returns the position of a substring in a string.
```
SELECT INSTR('Hello World', 'World') AS position;
```
SQL string functions for handling NULL values:
- Functions like COALESCE can be used to handle NULL values in string operations.
```
SELECT COALESCE(column1, 'DefaultValue') AS result
FROM example_table;
```