SQL Workout 3 - Data Transformation with String Manipulation Functions

Workouts SQL
1

Title: SQL Mastery: Data Transformation with String Manipulation Functions

Description:

In SQL, string functions play a pivotal role in data transformation and cleaning. Dive into the essentials of string manipulation and refine your SQL skills in this workout.

Scenario:

Imagine you’re working with a database table that contains user profiles. Some entries have inconsistent capitalization, extra spaces, or concatenated first and last names. Your task is to standardize and clean this data using SQL string functions.

Objectives:

By the end of this workout, you should be able to:

  1. Use SQL string functions to transform and clean data.

  2. Understand the application of various string functions in real-world scenarios.

  3. Enhance the readability and consistency of data in SQL tables.

Interactive Task:

Given your understanding of SQL, answer the following:

  1. If you want to convert a column username to all lowercase, which SQL function would you use?

    • Your Answer: ________________________

  2. How would you trim leading and trailing spaces from a column user_comment?

    • Your Answer: ________________________

  3. If you have a column full_name in the format “Lastname, Firstname” and you want to extract just the first name, which string functions might you use in combination?

    • Your Answer: ________________________

Questions:

  1. Which SQL function would you use to determine the length of a string in the column user_review?

    • i) LENGTH

    • ii) SIZE

    • iii) COUNT

    • iv) CHAR_LENGTH

  2. If you want to replace all occurrences of “apple” with “orange” in a column fruit_description, which function would you utilize?

    • i) REPLACE

    • ii) SWITCH

    • iii) CHANGE

    • iv) ALTER

Duration: 20 minutes

Difficulty: Intermediate

Period :
This workout will be released on Wednesday, September 20, 2023, and will end on Thursday, October 05, 2023. But you can always come back to any of the workouts and solve them.

2

Answer 1-Lower(username)
Answer 2 trim(user_comment)
Answer 3 Right( ‘Lastname,Firstsname’,10)
Anser 4 Length
answer 4 Replace ; select replace(‘Fruit_description’, ‘apple’ , ‘orange’)

3

Hi @EnterpriseDNA ,

Here is my solution to this workout:

Interactive Task:

  1. If you want to convert a column username to all lowercase, which SQL function would you use?

Answer:

  • LOWER(username) is used to convert the values in the username column to lowercase.
  • AS lowercase_username is used to give the resulting column a new name, which is optional but can be helpful for clarity in the query result.
  1. How would you trim leading and trailing spaces from a column user_comment ?

Answer:
SELECT TRIM(user_comment) AS trimmed_comment
FROM your_table;

  1. If you have a column full_name in the format “Lastname, Firstname” and you want to extract just the first name, which string functions might you use in combination?

Answer:
To extract just the first name from a column in the format “Lastname, Firstname,” you can use a combination of SQL string functions such as SUBSTRING(), CHARINDEX(), and LEN() (or LENGTH() depending on the SQL dialect you are using). Here’s an example:

sqlCopy code

SELECT SUBSTRING(full_name, CHARINDEX(',', full_name) + 2, LEN(full_name)) AS first_name
FROM your_table_name;

In this query:

  • CHARINDEX(',', full_name) is used to find the position of the comma in the full_name column. Adding 2 to it ensures that you start extracting characters right after the comma and the space.
  • SUBSTRING(full_name, ...) is used to extract the substring starting from the position after the comma and space to the end of the full_name column.
  • AS first_name is used to give the resulting column a new name, which is optional but can be helpful for clarity in the query result.

Replace your_table_name with the actual name of your table. This query will return the first names extracted from the full_name column.

Questions:

  1. Which SQL function would you use to determine the length of a string in the column user_review?

Answer:

  • iv) CHAR_LENGTH
  1. If you want to replace all occurrences of “apple” with “orange” in a column fruit_description, which function would you utilize?

Answer:

  • i) REPLACE

Thanks for the workout.
Keith

4
Summary

  1. If you want to convert a column username to all lowercase, which SQL function would you use?
    SELECT LOWER(username) FROM your_table;

  2. How would you trim leading and trailing spaces from a column user_comment ?
    SELECT TRIM(user_comment) FROM your_table;

  3. If you have a column full_name in the format “Lastname, Firstname” and you want to extract just the first name, which string functions might you use in combination?
    SUBSTRING and CHARINDEX

  4. Which SQL function would you use to determine the length of a string in the column user_review ?

  • i) LENGTH
  1. If you want to replace all occurrences of “apple” with “orange” in a column fruit_description , which function would you utilize?
  • i) REPLACE