4.2 String Functions: CONCAT, SUBSTRING, LENGTH

String functions in SQL allow you to manipulate and analyze text-based data. They are used to perform operations like concatenating strings, extracting substrings, and measuring the length of a string. These functions are essential when you need to work with text data in your queries, such as cleaning up user inputs, formatting results, or extracting specific parts of a string.

4.2.1 CONCAT() - Concatenate Strings

The CONCAT() function is used to concatenate (join) two or more strings into one continuous string. It is often used when you need to combine first and last names, or when creating full addresses from different parts.

• Purpose: To combine multiple strings into a single string.
• Syntax:

  SELECT CONCAT(string1, string2, ..., stringN) AS concatenated_string
  FROM table_name;
  

• Example:
  Combine first and last names into a full name:

  SELECT CONCAT(first_name, ' ', last_name) AS full_name
  FROM employees;
  

  This will result in a column full_name that contains the concatenation of first_name and last_name with a space in between.

4.2.2 SUBSTRING() - Extract Part of a String

The SUBSTRING() function allows you to extract a portion of a string based on a specified starting position and length. This is useful when you need to extract specific data from a longer text string, such as getting the first few characters of a product code or extracting the area code from a phone number.

• Purpose: To extract a specific portion of a string from a given starting position.
• Syntax:

  SELECT SUBSTRING(string, start_position, length) AS extracted_substring
  FROM table_name;
  

  • string: The string from which you want to extract a substring.
  • start_position: The position (index) from where the substring will start. The index starts from 1 (not 0).
  • length: The number of characters to extract. If omitted, it extracts the substring from the start position to the end of the string.
• Example:
  Extract the first 3 characters of a product code:

  SELECT SUBSTRING(product_code, 1, 3) AS short_code
  FROM products;
  

  This will return the first 3 characters of the product_code column.

4.2.3 LENGTH() - Measure the Length of a String

The LENGTH() function is used to find the number of characters in a string. It is useful when you want to measure the size of text fields, check if data is too long, or ensure that certain text constraints are met (such as a username or password length).

• Purpose: To return the length (number of characters) of a string.
• Syntax:

  SELECT LENGTH(string) AS string_length
  FROM table_name;
  

• Example:
  Find the length of a product description:

  SELECT LENGTH(description) AS description_length
  FROM products;
  

  This will return the length of the description field for each product.

4.2.4 Combining String Functions

You can also combine multiple string functions in a single query to perform more complex operations. For example, you can concatenate a string and extract a portion of it at the same time.

• Example:
  Combine first and last names, and then extract the first 5 characters of the full name:

  SELECT SUBSTRING(CONCAT(first_name, ' ', last_name), 1, 5) AS short_name
  FROM employees;
  

  This query first concatenates the first_name and last_name with a space in between, and then extracts the first 5 characters of the resulting full name.

4.2.5 Using String Functions for Data Cleaning

String functions are also useful in data cleaning and formatting tasks. For example, you might need to remove unwanted spaces from text or ensure that all text is in uppercase or lowercase.

• Example:
  Trim extra spaces from a column (Note: This function may vary depending on the database you use. In MySQL, it's TRIM()):

  SELECT TRIM(column_name) AS cleaned_column
  FROM table_name;
  

• Example:
  Convert a string to uppercase:

  SELECT UPPER(column_name) AS upper_case_column
  FROM table_name;
  

4.2.6 String Functions in Real-World Use Cases

1. Combining Address Components:
   If you have separate columns for street, city, and state in a table, you can use CONCAT() to create a full address string.
   • Example:

     SELECT CONCAT(street, ', ', city, ', ', state) AS full_address
     FROM customers;
     

2. Extracting Year from Date:
   You may need to extract the year from a date stored as a string. This can be done using SUBSTRING().
   • Example:

     SELECT SUBSTRING(order_date, 1, 4) AS order_year
     FROM orders;
     

3. Data Validation:
   Check if a string exceeds a certain length using LENGTH() to enforce data constraints.
   • Example:

     SELECT order_id, LENGTH(customer_name) AS name_length
     FROM orders
     WHERE LENGTH(customer_name) > 50;
     

4.2.7 Conclusion

String functions such as CONCAT(), SUBSTRING(), and LENGTH() are essential tools for manipulating and analyzing text data in SQL. By using these functions, you can perform tasks like combining columns, extracting specific parts of strings, and measuring string lengths. These functions play a crucial role in data processing, data cleaning, and formatting in real-world SQL applications.