SQL String Functions Explained: Examples for Better Understanding
2h ago Learning Mumbai 10 views Reference: 752409
Location: Mumbai
Price: Contact us
SQL string functions are used to manipulate and operate on string data types. Here’s an overview of some commonly used SQL string functions, along with examples to enhance your understanding:
1. CONCAT()
Combines two or more strings into one string.
Example:
```sql
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
```
2. SUBSTRING()
Extracts a portion of a string.
Example:
```sql
SELECT SUBSTRING(phone_number, 1, 3) AS area_code
FROM customers;
```
3. LENGTH()
Returns the length of a string.
Example:
```sql
SELECT LENGTH(email) AS email_length
FROM users;
```
4. UPPER() and LOWER()
Converts a string to uppercase or lowercase.
Example:
```sql
SELECT UPPER(first_name) AS upper_name, LOWER(last_name) AS lower_name
FROM employees;
```
5. TRIM()
Removes leading and trailing spaces from a string.
Example:
```sql
SELECT TRIM(leading ' ' FROM customer_name) AS trimmed_name
FROM customers;
```
6. REPLACE()
Replaces occurrences of a substring within a string.
Example:
```sql
SELECT REPLACE(email, '@example.com', '@newdomain.com') AS updated_email
FROM users;
```
7. CHARINDEX()
Returns the position of a substring within a string (available in SQL Server).
Example:
```sql
SELECT CHARINDEX('Smith', last_name) AS position
FROM employees;
```
8. LEFT() and RIGHT()
Returns a specified number of characters from the left or right side of a string.
Example:
```sql
SELECT LEFT(first_name, 3) AS first_three_letters,
RIGHT(last_name, 4) AS last_four_letters
FROM employees;
```
9. FORMAT()
Formats a value to a specified format (available in SQL Server).
Example:
```sql
SELECT FORMAT(order_date, 'yyyy-MM-dd') AS formatted_date
FROM orders;
```
10. FIND_IN_SET()
Finds a value within a set of comma-separated values (available in MySQL).
Example:
```sql
SELECT FIND_IN_SET('apple', fruits) AS position
FROM fruit_table;
```
Summary
Using these string functions, you can manipulate text data in various ways to suit your analytical needs. Practice with these functions on sample datasets to better grasp their utility in real-world SQL scenarios. If you have specific use cases or examples in mind, feel free to share!
