16-SQL-String-Function

SQL String Functions

SQL provides various string functions that allow you to manipulate and work with string data. Here are some commonly used SQL string functions:

1️⃣ CHARACTER_LENGTH()

This function returns the number of characters in a string.

2️⃣ UPPER()

This converts text to uppercase.

3️⃣ LOWER()

This converts text to lowercase.

4️⃣ LEFT()

This returns characters from the left side of a string.

5️⃣ RIGHT()

This returns characters from the right side of a string.

6️⃣ SUBSTRING()

Extracts part of a string.

7️⃣ CONCAT()

This joins multiple strings together.

USE market_star_schema;

-- find the character length of the string "Data Analysis"
SELECT CHARACTER_LENGTH("Data Analysis");

-- find the minimum and maximum character lengths of customer name
SELECT MIN(CHARACTER_LENGTH(customer_name)), MAX(CHARACTER_LENGTH(customer_name))
FROM cust_dimen;

-- convert the customer name to uppercase and lowercase
SELECT customer_name,
UPPER(customer_name), LOWER(customer_name)
FROM cust_dimen;

-- find the first 3 characters of the city name.
SELECT LEFT("Hyderabad",3);

-- find the city name that Start from position 2 Extract 3 characters
SELECT city, SUBSTRING(city, 2, 3)
FROM cust_dimen;

-- find the city name that start from position 2 Extract all characters
SELECT city, SUBSTRING(city,2) 
FROM cust_dimen;

-- find the city name that start from position -3 EXtract 4 characters
SELECT city, SUBSTRING(city,-3,4) 
FROM cust_dimen;

-- concatenate the stings "Data Analysis" and "!" with a space in between
SELECT CONCAT("Data"," ","Analysis"," !");

-- concatenate the city and state with a hyphen('-') in between
SELECT city, state, CONCAT(city, "-",state)
FROM cust_dimen;

-- concat the first three letters and last three letters of state
SELECT state,
CONCAT(LEFT(state,3),RIGHT(state,3)) AS result,
CHARACTER_LENGTH(CONCAT(LEFT(state,3),RIGHT(state,3))) AS count
FROM cust_dimen;