SELECT LEFT(product_code, 3) AS category_code FROM products;

If product_code = ELE12345, Result: ELE.

This can be useful when codes contain meaningful prefixes.

👉 10. RIGHT()

Returns characters from the end of a string.

SELECT RIGHT(account_number, 4) AS last_four_digits FROM accounts;

Example: 1234567890, Result: 7890.

This is commonly useful for reporting or identifying records without displaying the complete identifier.

🔗 11. CONCAT()

Combines multiple strings.

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

Example: first_name = Alice, last_name = Smith, Result: Alice Smith.

⚠️ 12. CONCAT vs + Operator

Some SQL dialects allow string concatenation using operators such as first_name + ' ' + last_name while others use first_name || ' ' || last_name.

CONCAT() provides a more portable and readable approach, although NULL behavior can still vary by database.

🔄 13. REPLACE()

Replaces one piece of text with another.

SELECT REPLACE(phone, '-', '') AS cleaned_phone FROM customers;

Example: 987-654-3210 becomes 9876543210.

SELECT REPLACE(product_name, 'Old', 'New') AS updated_name FROM products;

📧 14. Extracting Information from Email Addresses

Suppose email = 'alice@gmail.com'. You may want to identify the domain.

One approach is database-specific string manipulation.

For example, in PostgreSQL:

SELECT SPLIT_PART(email, '@', 2) AS email_domain FROM customers;

Result: gmail.com

This is useful for:

• Customer segmentation

• Domain analysis

• Corporate vs personal email analysis

• Detecting invalid domains

📊 15. Grouping Customers by Email Domain

Once you extract the domain, you can aggregate it.

SELECT
    SPLIT_PART(LOWER(TRIM(email)), '@', 2) AS email_domain,
    COUNT(*) AS customer_count
FROM customers
WHERE email IS NOT NULL
GROUP BY SPLIT_PART(LOWER(TRIM(email)), '@', 2)
ORDER BY customer_count DESC;

This combines several concepts:

TRIM() → LOWER() → SPLIT_PART() → GROUP BY → COUNT() → ORDER BY

This is much closer to real-world analytics work.

🔎 16. POSITION()

POSITION() finds where a substring occurs.

SELECT POSITION('@' IN email) AS at_position FROM customers;

For alice@gmail.com it returns the position of @.

This can help identify whether a string contains a particular character.

🧪 17. String Functions for Data Validation

Suppose you want to identify potentially invalid emails.

SELECT * FROM customers WHERE email IS NOT NULL AND POSITION('@' IN email) = 0;

This doesn't prove an email is valid, but it can identify obviously problematic records.

For serious validation, application-level validation or dedicated data-quality tools may be more appropriate.

🏷️ 18. Standardizing Categories

Suppose your database contains Premium, premium, PREMIUM, Premium. These may represent the same business category.

You can standardize them:

SELECT UPPER(TRIM(customer_type)) AS standardized_type FROM customers;

Now they all become PREMIUM.

This is particularly useful before grouping.

📈 19. String Functions + GROUP BY

Without cleaning:

SELECT customer_type, COUNT(*) AS customer_count FROM customers GROUP BY customer_type;