Ch 8 · SQL String Functions · SQL
Topic 8 of 8 in SQL — Query a Real Database — 4 lessons.
String Manipulation
Manipulate text data with ||, SUBSTR, TRIM, REPLACE, and more. The || operator joins pieces of text, UPPER and LOWER change case, SUBSTR pulls out part of a string, LENGTH counts characters, TRIM strips spaces from the ends and REPLACE swaps one piece of text for another. LIKE checks text against a simple pattern. These tools are essential for tidying messy real-world data.
String Manipulation in Code
Each example applies a string function to real columns. || builds a full name with a space in between, UPPER and LOWER change case, SUBSTR takes the first letter of each name to make initials, LENGTH counts characters, and TRIM and REPLACE tidy up the messy contacts table. Notice that SUBSTR starts counting at 1, not 0, and that LIKE checks the end of an email address.
Practice: String Manipulation
Put the string functions to work with three small tasks: build full names from first and last names, make each employee's initials from the first letters of their names and find employees with Gmail addresses. A correct answer gives its new columns clear names, counts string positions from 1, and uses a pattern that matches only emails ending in the Gmail domain.
Solution & Challenge: String Manipulation
Compare your queries with the worked solution for full names, initials and Gmail users. The challenge then asks you to clean a messy contacts table: capitalise names properly, format UK phone numbers consistently and check that emails look valid. A good answer combines several string functions inside UPDATE statements and uses LIKE and INSTR to spot badly formed email addresses.