Learning of SQL Day 12

 

Day 12: String Functions

Introduction:

• String functions in SQL are used to manipulate string data. They are essential for transforming and analyzing text.

Key Concepts:

• LENGTH(): Returns the length of a string.

• LOWER() and UPPER(): Converts a string to lower-case or upper-case.

• CONCAT(): Concatenates two or more strings.

• SUBSTRING(): Extracts a substring from a string.

• TRIM(): Removes leading and trailing spaces from a string.

• REPLACE(): Replaces occurrences of a substring within a string.

SQL Commands and Examples:

1. LENGTH() Function:

   • Get the length of a string.

sql

-- Get the length of the CourseName string
SELECT CourseName, LENGTH(CourseName) AS NameLength FROM Courses;

2. LOWER() and UPPER() Functions:

   • Convert a string to lower-case or upper-case.

sql

-- Convert the FirstName to lower-case and upper-case
SELECT FirstName, LOWER(FirstName) AS LowerCaseName, UPPER(FirstName) AS UpperCaseName FROM Students;

3. CONCAT() Function:

   • Concatenate two or more strings.

sql

-- Concatenate FirstName and LastName to create FullName
SELECT FirstName, LastName, CONCAT(FirstName, ' ', LastName) AS FullName FROM Students;

4. SUBSTRING() Function:

   • Extract a substring from a string.

sql

-- Extract the first 3 characters from the CourseName
SELECT CourseName, SUBSTRING(CourseName, 1, 3) AS ShortName FROM Courses;

5. TRIM() Function:

   • Remove leading and trailing spaces from a string.

sql

-- Remove leading and trailing spaces from the CourseName
SELECT CourseName, TRIM(CourseName) AS TrimmedName FROM Courses;

6. REPLACE() Function:

   • Replace occurrences of a substring within a string.

sql

-- Replace 'SQL' with 'Structured Query Language' in the CourseName
SELECT CourseName, REPLACE(CourseName, 'SQL', 'Structured Query Language') AS NewName FROM Courses;

Practice Exercise:

1. Use the Courses and Students tables.

2. Write a query to get the length of each FirstName.

3. Write a query to convert the LastName to upper-case.

4. Write a query to concatenate FirstName and LastName with a space in between.

5. Write a query to extract the first 5 characters from the CourseName.

6. Write a query to trim leading and trailing spaces from the FirstName.

7. Write a query to replace 'Course' with 'Subject' in the CourseName.

sql

-- Get the length of each FirstName
SELECT FirstName, LENGTH(FirstName) AS NameLength FROM Students;

-- Convert LastName to upper-case
SELECT LastName, UPPER(LastName) AS UpperCaseLastName FROM Students;

-- Concatenate FirstName and LastName
SELECT FirstName, LastName, CONCAT(FirstName, ' ', LastName) AS FullName FROM Students;

-- Extract the first 5 characters from CourseName
SELECT CourseName, SUBSTRING(CourseName, 1, 5) AS ShortName FROM Courses;

-- Trim leading and trailing spaces from FirstName
SELECT FirstName, TRIM(FirstName) AS TrimmedName FROM Students;

-- Replace 'Course' with 'Subject' in CourseName
SELECT CourseName, REPLACE(CourseName, 'Course', 'Subject') AS NewCourseName FROM Courses;

Mastering string functions allows you to manipulate text data effectively.