STRING FUNCTIONS

This lesson on STRING FUNCTIONS is hands-on and example-driven. You will learn to manipulate, clean, and combine text data within your SQL queries using powerful string functions. This mastery allows you to standardize data formats, extract key information, and prepare raw text fields for analysis or display. By the end of this lesson, you can confidently transform raw text into structured, usable data.

What You'll Be Able To Do

• Calculate the exact character count of any string field using LENGTH().
• Standardize text casing across entire columns using UPPER() and LOWER().
• Isolate specific segments of text using LEFT(), RIGHT(), and SUBSTRING().
• Clean data by removing unwanted leading, trailing, or bilateral whitespace using TRIM variants.
• Merge multiple data fields into a single display column using CONCAT().
• Dynamically modify string content using REPLACE() and find character positions using LOCATE().

Detailed Concept Walkthrough

1. Precise String Extraction

Extraction functions pull specific segments of text based on position or length. This is crucial for parsing structured data embedded within larger text fields, such as codes or identifiers.

• Mechanism: SUBSTRING requires a starting position (1-based index) and the number of characters to return. LEFT/RIGHT only require the total character count.
• Under the Hood: The database engine counts characters sequentially from the start (LEFT) or end (RIGHT) of the string to fulfill the requested length.
• Syntax Rule: SQL string functions are typically 1-indexed (the first character is position 1), unlike many programming languages which are 0-indexed.
• Best Practice: Always test SUBSTRING with known data to ensure the 1-based index and length arguments correctly isolate the desired segment.

SELECT SUBSTRING(first_name, 3, 2) FROM employee_demographics; -- Extracts 2 characters starting at position 3

Key Takeaway: Use SUBSTRING for middle segments, and LEFT/RIGHT for segments anchored to the start or end.

2. Cleaning Whitespace and Casing

Data quality requires removing extraneous spaces (TRIM) and enforcing consistent casing (UPPER/LOWER) before comparison or display. This standardization prevents matching errors.

• Mechanism: TRIM removes spaces from both ends by default. LTRIM and RTRIM offer granular control, removing only leading or only trailing spaces, respectively.
• Best Practice: Always use TRIM() on user-input fields before storing or comparing them to prevent matching errors caused by hidden spaces.
• Under the Hood: Casing functions (UPPER/LOWER) iterate through the string, applying character-set specific conversion rules to each letter.
• Nuance: TRIM only removes spaces from the ends; it does not remove extra spaces that exist between words within the string.

SELECT UPPER(TRIM(' sky ')); -- Returns 'SKY'

Key Takeaway: Standardize casing and remove whitespace early in your data processing pipeline for reliable comparisons.

3. Merging Fields and Finding Positions

CONCAT joins multiple columns or literal strings into a single output field, essential for creating full names or formatted addresses. LOCATE finds where a specific pattern begins.

• Mechanism: CONCAT takes an arbitrary number of arguments and returns them joined end-to-end. LOCATE returns the starting position (1-based index) of the first occurrence of the search string.
• Best Practice: When using CONCAT for names, explicitly include separator characters (like a space) to ensure readability.
• Execution Flow: If LOCATE cannot find the substring, it returns 0, which can be used in conditional logic (e.g., WHERE clauses).
• Under the Hood: REPLACE iterates through the string, substituting every instance of the 'old' string with the 'new' string before returning the result.

SELECT CONCAT(first_name, ' ', last_name) AS FullName FROM employee_demographics;

Key Takeaway: Use CONCAT to construct display-ready fields and LOCATE to enable conditional logic based on substring presence.

Topics Covered in STRING FUNCTIONS

1. LENGTH() Function (0:21 - 1:47) — The LENGTH function returns the total number of characters in the specified string or column value.
2. Casing Functions (1:48 - 3:01) — UPPER and LOWER functions are used to standardize the casing of text data for consistency.
3. TRIM Variants (3:02 - 4:20) — TRIM, LTRIM, and RTRIM are used to remove unwanted whitespace from the ends of a string.
4. LEFT and RIGHT (4:21 - 6:14) — These functions extract a specified number of characters starting from either the beginning or the end of the string.
5. SUBSTRING Function (6:15 - 8:13) — SUBSTRING extracts a portion of a string based on a starting position and a defined length.
6. REPLACE Function (8:14 - 9:10) — REPLACE substitutes all occurrences of a specific character or sequence with a new one.
7. LOCATE Function (9:11 - 10:18) — LOCATE finds and returns the 1-based starting position of a substring within a larger string.
8. CONCAT Function (10:19 - 11:28) — CONCAT combines multiple columns or strings into a single, merged output field.
9. Other Function Types (11:35) — The lesson concludes by mentioning other function categories like Numeric and Date functions.

SQL Cheat Sheet

• LENGTH(string) — Returns the number of characters in the string

  SELECT LENGTH('Skyfall');
  

• UPPER(string) — Converts all characters in the string to uppercase

  SELECT UPPER('Sky');
  

• TRIM(string) — Removes leading and trailing whitespace

  SELECT TRIM(' sky ');
  

• LEFT(string, length) — Extracts characters from the start of a string

  SELECT LEFT(first_name, 4);
  

• SUBSTRING(s, start, len) — Extracts a portion starting at position start

  SELECT SUBSTRING('Alexander', 3, 2);
  

• REPLACE(s, old, new) — Substitutes all occurrences of old with new

  SELECT REPLACE(first_name, 'a', 'z');
  

• LOCATE(sub, string) — Finds the 1-based starting position of a substring

  SELECT LOCATE('x', 'Alexander');
  

• CONCAT(s1, s2, ...) — Joins multiple strings or columns together

  SELECT CONCAT(first_name, ' ', last_name);
  

Comparison Table

Common Pitfalls

• Mistake: Assuming string indexing starts at 0 for SUBSTRING or LOCATE. Avoid: Remember that standard SQL string functions are 1-indexed.
• Mistake: Using CONCAT without explicitly adding spaces between fields. Avoid: Always include a literal space string (' ') as an argument in CONCAT for readability.
• Mistake: Using TRIM() to remove spaces within a string (e.g., double spaces). Avoid: TRIM only affects leading/trailing spaces; use REPLACE() to handle internal spaces.
• Mistake: Confusing the arguments for SUBSTRING (start position vs. length). Avoid: The second argument is the starting position, the third is the total length to extract.

FAQs

• Do these functions modify the data in the underlying table? No, string functions only manipulate the data returned in the result set; they do not change the underlying table data.
• What happens if LOCATE() doesn't find the substring I am searching for? LOCATE() returns 0 if the specified substring is not found within the target string, allowing you to filter based on its absence.
• Can I use string functions in WHERE clauses or only in SELECT statements? You can use string functions in both SELECT statements (for display) and WHERE clauses (for filtering records based on string properties).