SQL Intermediate: String Functions

Name: SQL Intermediate: String Functions
Author: Eric Grose

Manipulate text in SQL with string functions

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

In these lessons, you'll gain expertise using all the available string functions in SQL. In this lesson, we'll learn about the importance of string functions in SQL and how they can be used to manage and manipulate text data. When we're working with databases, we often deal with data types that are text or string based. Examples of these can be names, addresses, descriptions, or even books. Let's start this lesson by creating a simple table called books. This table will contain a book ID, which will be our primary key, as well as some general information about the book, including the title, the author, and the genre. Let's go ahead and create this table by pressing Ctrl -Enter. Now that we've created our books table, let's use an Insert Into statement to insert some rows. As we can see here, this table contains a bunch of text data that we can use to mine for insights. Let's go ahead and add these rows by pressing Ctrl -Enter. We can use the reverse function, and as the name suggests, to reverse a string. So for this query, we'll take the title of the book and the reverse title of the book. Let's go ahead and run this query and see how the reverse function works. Go ahead and press Ctrl -Enter. Now this isn't the most practical example for this table, but we do know that this is an available string function that can come down in our analysis path at some point. Next up is the write function. This function returns the rightmost characters from a string. So for this query, we'll select the author column. We'll use the write function on the author column, and we'll use a comma and list the number of characters we want to extract from the author's name. And with the write function, we use a comma and list the number of characters we want to extract. In this case, three. So we'll go ahead and alias, or give this column a new name, last three characters. Let's go ahead and run this query by pressing Ctrl -Enter. And we can see here, from our original author column, we're pulling in the last three strings of our author column using the write function. Now, let's look at the lpad and rpad functions, which allow us to pad a string with specific characters. The lpad function pads a string from the left, while the rpad pads a string from the right. So for this query, let's use the lpad function on the book ID column. Let's say we want our book IDs to be three characters, and we'll go ahead and pad it, or add this value of a zero. We'll go ahead and alias this column as padded book ID, as well as add the title so we know which book we're looking at. Let's go ahead and run this to see this query in action. Go ahead and press Ctrl -Enter. And we see here, our original book ID of one, two, three, four, five, has now been added two zeros for three total characters. The locate function can be used to find a position of a substring within a string. So for this query, we'll select the title column. We'll use the locate function to find the word the. Within the locate function, we'll use a comma in the column of interest, in this case, the title. And we'll go ahead and rename this column position. Let's go ahead and see this query in action by pressing Ctrl -Enter. And in our output here, we see the titles that don't contain the word the are listed at position zero. And then the one row that does contain it is listed at position one, or the first word of our substring. Last, let's discuss our ELT function. The ELT function returns a string at the index number provided. So if the index number is less than one, or greater than the number of arguments, the ELT returns a null. Within our books table, the book ID column is suitable for the ELT function. So for this query, we'll return the title. We'll use the ELT function based on the book ID, or our index. And then just for education purposes, we'll go ahead and assign this text to each one of our book IDs. And we'll label this column rating, using our as alias keyword. Let's go ahead and run this by pressing Ctrl -Enter. And we see here now we have assigned our rating column to our books data set. Now that we've explored the range of useful string functions, you can add some flexibility and power to your SQL tool set. Thanks for watching. Stay tuned for the next lesson, where we learn seven additional string functions.

Intermediate Provider : Eric Grose 3 Lessons · 17m in Arabic, English

Course Description

In these lessons, you will come to understand the importance and wide-ranging applications of SQL string functions. We will begin by introducing the significance of string functions in SQL and how they're used in managing and manipulating text data. From there, we'll delve deeper into common SQL string functions like CONCAT, SUBSTRING, CHAR_LENGTH, UPPER, LOWER, TRIM, and REPLACE, offering a clear understanding of how each function operates. Additionally, these lessons provide you with ample opportunities to practice these functions by writing queries and handling real-world scenarios.

In these lessons on string functions, you will learn about the powerful functions available in SQL for managing and manipulating text data. We will explore the utility and applications of string functions, learn about common SQL string functions like CONCAT, SUBSTRING, CHAR_LENGTH, UPPER, LOWER, TRIM, and REPLACE, and gain hands-on practice with these functions through real-world scenarios. By the end of these lessons, you will be proficient in using SQL string functions to handle various data manipulation tasks.

What You'll Learn

Understand the importance of manipulating text data using SQL string functions
Apply common SQL string functions including CONCAT, SUBSTRING, CHAR_LENGTH, UPPER, LOWER, TRIM, and REPLACE
Write SQL queries to manage and manipulate text data
Practice string functions through real-world scenarios
Develop proficiency in handling various data manipulation tasks with SQL string functions

Key Takeaways

SQL string functions are used to manage and manipulate text data within a database.
Common SQL string functions covered include CONCAT, SUBSTRING, CHAR_LENGTH, UPPER, LOWER, TRIM, and REPLACE.
The lessons provide hands-on practice writing queries to handle real-world scenarios.
By the end of the lessons, learners are proficient in using SQL string functions for various data manipulation tasks.

Frequently Asked Questions

What SQL string functions does this course cover?

It covers common SQL string functions including CONCAT, SUBSTRING, CHAR_LENGTH, UPPER, LOWER, TRIM, and REPLACE.

What will I be able to do after completing these lessons?

You will be proficient in using SQL string functions to handle various data manipulation tasks, including writing queries to manage and manipulate text data in real-world scenarios.

Does this course include hands-on practice?

Yes. The lessons provide ample opportunities to practice these functions by writing queries and handling real-world scenarios.

What topics are included in the lessons?

The lessons are Introduction to String Functions, Common SQL String Functions, and Utilizing String Functions in SQL.

What skills does this course develop?

It develops skills in Data Manipulation, Database Programming, Extended SQL, SQL (Programming Language), Sql2016, and Standard SQL.