Blog

SQL: CONCAT, LENGTH, and SUBSTRING

If you’ve ever dabbled in databases, you’ve likely come across SQL, the language used to manage and manipulate data. But what happens when you need to handle text stored in your database? Enter SQL string functions CONCAT, LENGTH, and SUBSTRING! These handy tools let you combine, measure, and extract parts of text in your SQL databases. Whether you’re piecing together first and last names, calculating how long a string is, or grabbing just a snippet, these functions have got you covered. Ready to dive into the magic world of SQL and level up your text-handling skills? Let’s get started!

Understanding SQL String Functions

SQL string functions are built-in functions that allow users to manipulate and process text data stored in databases. These functions help in formatting, extracting, modifying, and analyzing string values efficiently. They are particularly useful when handling customer names, addresses, logs, and other textual information.

For example, functions like CONCAT merge multiple strings, LENGTH calculates string length, and SUBSTRING extracts a portion of text. These functions play a crucial role in querying databases, making data readable, and transforming raw data into meaningful insights. By using SQL string functions, developers can clean, format, and standardize textual information, improving the overall efficiency and accuracy of database operations.

Detailed Overview of Key String Functions

1. CONCAT Function

The CONCAT function in SQL is used to combine two or more strings into a single string. It is commonly used for merging names, constructing messages, or formatting output dynamically.

Syntax:

SELECT CONCAT('John', ' ', 'Doe') AS full_name;

Output:

full_name  
-----------
John Doe
CONCAT_WS (Concatenate with Separator)

Unlike CONCAT, CONCAT_WS allows adding a separator between the concatenated strings.

Syntax:

SELECT CONCAT_WS('-', '2025', '01', '10') AS formatted_date;

Output:

formatted_date  
---------------
2025-01-10

This is useful for formatting dates, paths, and structured data where separators are needed.

2. LENGTH Function

The LENGTH function returns the number of characters (including spaces) in a string. It is useful for validating input length, truncating data, and optimizing storage.

Syntax:

SELECT LENGTH('SQL Functions') AS length_value;

Output:

length_value  
--------------
13
Dialect Differences:
  • In MySQL and MariaDB, LENGTH returns the number of bytes, so multibyte characters may affect results.
  • In PostgreSQL and SQL Server, LENGTH (or LEN in SQL Server) returns the number of characters, regardless of byte size.

Example in SQL Server:

SELECT LEN('SQL Functions') AS length_value;

3. SUBSTRING Function

The SUBSTRING function extracts a specific portion of a string, starting from a given position for a specified length. It is widely used for extracting first names, abbreviations, or specific data segments.

Syntax:

SELECT SUBSTRING('Database Management', 1, 8) AS extracted_text;

Output:

extracted_text  
---------------
Database
Practical Use Cases:
  • Extracting the first 3 characters of a product code: SELECT SUBSTRING('PQR-98765', 1, 3) AS product_prefix; Output: product_prefix -------------- PQR
  • Extracting domain names from email addresses: SELECT SUBSTRING('user@example.com', LOCATE('@', 'user@example.com') + 1, LENGTH('user@example.com')) AS domain; Output: domain -------- example.com

These string functions help in efficient text manipulation, improving data handling and transformation in SQL queries.

Additional Useful String Functions

1. UPPER and LOWER Functions

The UPPER and LOWER functions are used to convert text to uppercase and lowercase, respectively. These functions are useful for standardizing user input, performing case-insensitive comparisons, and formatting output.

Syntax:

SELECT UPPER('sql functions') AS uppercase_text;
SELECT LOWER('SQL FUNCTIONS') AS lowercase_text;

Output:

uppercase_text  
--------------
SQL FUNCTIONS  

lowercase_text  
--------------
sql functions
Use Case:
  • Ensuring case consistency when storing or comparing email addresses. SELECT LOWER(email) FROM users;

2. TRIM, LTRIM, and RTRIM Functions

These functions remove unwanted spaces from strings.

  • TRIM removes spaces from both sides.
  • LTRIM removes spaces from the left.
  • RTRIM removes spaces from the right.

Syntax:

SELECT TRIM('   SQL   ') AS trimmed_text;
SELECT LTRIM('   SQL') AS left_trimmed;
SELECT RTRIM('SQL   ') AS right_trimmed;

Output:

trimmed_text  
-------------
SQL  

left_trimmed  
-------------
SQL  

right_trimmed  
-------------
SQL
Use Case:
  • Cleaning user input before inserting into a database. INSERT INTO users (name) VALUES (TRIM(' John Doe '));

3. REPLACE Function

The REPLACE function replaces occurrences of a substring within a string with another substring. It is useful for formatting text, correcting typos, and modifying stored data.

Syntax:

SELECT REPLACE('Hello World', 'World', 'SQL') AS replaced_text;

Output:

replaced_text  
--------------
Hello SQL
Use Case:
  • Replacing underscores with spaces in a dataset. SELECT REPLACE('SQL_Functions_Tutorial', '_', ' ') AS formatted_text;

These string functions improve text processing efficiency, ensuring cleaner and more readable data in SQL queries.

Real-Life Uses of SQL String Functions CONCAT, LENGTH, and SUBSTRING

Sure, let’s dive into the fascinating world of SQL string functions by focusing on some real-life scenarios where companies or brands have used SQL string functions CONCAT LENGTH SUBSTRING to solve practical problems:


  1. Customer Data Integration:
    A leading e-commerce company needed to create a single customer identifier by joining different columns containing first name, last name, and customer ID. By using the CONCAT function, they were able to merge these columns seamlessly into a single string, making customer data integration more efficient.

  2. Content Length Monitoring:
    A popular social media platform used the LENGTH function to enforce character limits on user posts. The team set guidelines to ensure that posts were concise yet informative by regularly checking the character count. This made it easier to maintain readability and ensured content remained within the system’s constraints.

  3. Targeted Marketing Campaigns:
    A telecommunications giant implemented the SUBSTRING function to extract area codes from customer phone numbers. By analyzing these specific substrings, the company could tailor their marketing efforts to particular regions, thereby enhancing the effectiveness of their promotional campaigns and maximizing outreach.

  4. Data Clean-up and Formatting:
    A financial institution used a combination of SUBSTRING and CONCAT functions to clean up customer records and format account numbers correctly. By slicing unnecessary characters and stitching the needed pieces together, they improved data consistency and reliability.

  5. Product Code Generation:
    A clothing retailer uses the LENGTH function to verify product codes’ lengths before they are assigned. By ensuring each code is of a consistent length, inventory management becomes simpler, and errors are minimized.

These real-world applications highlight the versatility and practicality of employing SQL string functions CONCAT LENGTH SUBSTRING across various industries. Aren’t they fascinating?

Test Your Knowledge: Quiz on SQL String Functions CONCAT, LENGTH, SUBSTRING

  1. What does the CONCAT function do in SQL?

    A. Joins two or more strings together
    B. Counts the number of characters in a string
    C. Extracts a specific part of a string


  2. Which SQL function would you use to find the length of a string?

    A. CONCAT
    B. LENGTH
    C. SUBSTRING

  3. How would you extract the substring “hel” from the string “hello” in SQL?

    A. CONCAT(“hello”, 1, 3)
    B. LENGTH(“hello”)
    C. SUBSTRING(“hello”, 1, 3)

  4. What result does LENGTH(‘Coding’) return?

    A. 7
    B. 6
    C. 5
  5. Can CONCAT combine numbers and strings in SQL?

    A. No, only strings
    B. Yes, but numbers are converted to strings
    C. Yes, without any conversion


Curious to test this out yourself? With our AI-powered SQL online compiler, you can instantly write, run, and test your code. It’s interactive and gives you instant results―making learning SQL a breeze! Isn’t that handy for practice?

Common Pitfalls and Best Practices

Common Mistakes When Using String Functions

  1. Ignoring NULL Values
    • SQL string functions return NULL if any argument is NULL.
    • Mistake: SELECT CONCAT('Hello', NULL, 'World'); Output: NULL
    • Fix: Use COALESCE() to handle NULL values. SELECT CONCAT('Hello', COALESCE(NULL, ''), 'World'); Output: HelloWorld
  2. Confusion Between LENGTH and CHAR_LENGTH
    • LENGTH() returns bytes, while CHAR_LENGTH() returns characters (important for multibyte characters).
    • Mistake in UTF-8 databases: SELECT LENGTH('你好'); -- Returns 6 (bytes) SELECT CHAR_LENGTH('你好'); -- Returns 2 (characters)
  3. Improper Use of SUBSTRING Indexing
    • SQL string indexes start from 1, not 0 (unlike many programming languages).
    • Mistake: SELECT SUBSTRING('SQL Functions', 0, 3); Fix: SELECT SUBSTRING('SQL Functions', 1, 3); -- Returns 'SQL'
  4. Using REPLACE Instead of REGEXP_REPLACE for Complex Replacements
    • REPLACE() is case-sensitive and cannot use patterns.
    • Fix: Use REGEXP_REPLACE() (available in MySQL 8.0+, PostgreSQL). SELECT REGEXP_REPLACE('Hello SQL', 'SQL', 'World', 'i');

Best Practices for Efficient String Manipulation

  1. Use Proper Data Types
    • Store fixed-length data in CHAR(n) instead of VARCHAR(n) when possible for performance benefits.
  2. Avoid Excessive Function Nesting
    • Instead of: SELECT UPPER(TRIM(REPLACE(name, '_', ' '))) FROM users;
    • Use clear, separate operations for better readability and maintainability.
  3. Optimize Queries with Indexing
    • String functions inside WHERE conditions can prevent index usage.
    • Inefficient: SELECT * FROM users WHERE UPPER(name) = 'JOHN DOE';
    • Optimized: SELECT * FROM users WHERE name = 'John Doe';

By following these best practices, developers can avoid common pitfalls and write optimized, error-free SQL queries for string manipulation.

Conclusion

Wrapping up, SQL string functions like CONCAT, LENGTH, and SUBSTRING are essential tools for managing text in databases. To dive deeper into these concepts, check out Newtum. Keep exploring and practicing to hone your skills. Don’t hesitate to experiment—it’s the best way to learn!

Edited and Compiled by

This blog was compiled and edited by Rasika Deshpande, who has over 4 years of experience in content creation. She’s passionate about helping beginners understand technical topics in a more interactive way.

About The Author

Rasika Deshpande

Rasika Deshpande, a passionate writer with over 4 years of experience in the EdTech industry. At Newtum, she writes, edit, and compile engaging blog and other content. Writing is not just a profession for her; it’s a passion that fuels creativity and dedication to the field of writing.

See author's posts