“`html

String manipulation is a frequent operation in SQL, and one of the most commonly employed functions for this task is SUBSTRING. It aids in extracting a specific segment of text from a larger string, which is particularly advantageous when cleansing, formatting, or examining data. SQL SUBSTRING enables the isolation of specific characters from a string, allowing users to extract or report on precise segments of the data. The function necessitates three primary arguments: the string to extract from, the starting point of the extraction, and the count of characters to retrieve. In this article, you’ll discover what the SQL SUBSTRING function is and how it operates alongside examples and their performance evaluation.

Table of Contents:

• What is SQL SUBSTRING?
• Syntax and Parameters of SQL SUBSTRING
• When to Use SQL SUBSTRING?
• How to Use SQL SUBSTRING in Queries?
• Advanced Use Cases of SQL SUBSTRING
• SQL SUBSTRING vs Other String Functions
• Common SQL SUBSTRING Errors and How to Avoid Them
• Best Practices for Using SQL SUBSTRING
• Conclusion

What is SQL SUBSTRING?

SQL SUBSTRING is an in-built function utilized to extract a distinct part of a text string according to the starting point and a specified length. It is predominantly supported by relational databases such as MySQL, SQL Server, PostgreSQL, and Oracle, and is also employed in data manipulation. This function plays a crucial role in tasks involving data manipulation, cleansing, and transformation. By utilizing SUBSTRING, you can effectively retrieve significant segments from larger text fields, facilitating easier analysis and handling of textual data.

Syntax and Parameters of SQL SUBSTRING

The general syntax of the SQL SUBSTRING() function: 

SUBSTRING(string_expression, start_position, length)

Explanation: 

• String_expression: The primary string from which characters will be drawn.
• Start_position: The index from which to begin extracting characters, based on a 1-based index (counting starts at 1, not 0).
• Length: The quantity of characters to extract starting from the start position.

When to Use SQL SUBSTRING?

Employing SQL SUBSTRING() optimizes data preparation and enhances the quality of data analysis, and it can be applied when:

1. You need to extract a segment of a string for data presentation.
2. You wish to cleanse or format unprocessed text data.
3. You are analyzing specific fields from concatenated strings.
4. You are managing unstructured text or logs.
5. You require precise control over text data for the migration or ETL operation.

1. Extracting a Segment of a String Using SQL SUBSTRING

Before extracting data, let’s establish a dataset to illustrate the SQL SUBSTRING() function.

CREATE TABLE Company_name (
    ID INT,
    FullText VARCHAR(50),
    PhoneNumber VARCHAR(20)
);
-- Insert data into the table
INSERT INTO Company_name (ID, FullText, PhoneNumber)
VALUES
(1, 'ASKRUDRG Pvt Limited', '+1-543-1265486'),
(2, 'PGDRTVRSEH Pvt Limited', '+1-075-7578876');
SELECT * FROM Company_name;

Output:

This is the table following its creation, and now to extract a segment of a string, you can use the SQL SUBSTRING() function to define the starting index and length of the desired substring.

Example: 

SELECT 
    ID,
    SUBSTRING(FullText, 1, 6) AS ExtractedText
FROM 
   Company_name;

Output:

Explanation: In this instance, the SUBSTRING() function extracted the initial 6 characters from the full text by constraining the size of the string.

2. Formatting or Cleansing Data with the SQL SUBSTRING

The SUBSTRING function in SQL is frequently used to format or clean data within a table. It is particularly beneficial when you need to isolate only the pertinent segment of a string while disregarding extraneous details. In data warehousing and ETL initiatives, SUBSTRING() is commonly employed to isolate specific aspects of a value, such as extracting a date, code, or identifier from a larger text field. This function streamlines SQL string manipulation and enhances overall data quality. Utilizing SUBSTRING() effectively can diminish complexity and expedite data processing.

Example:

SELECT 
    ID,
    SUBSTRING(PhoneNumber, 8, 6) AS LocalNumber
FROM 
    Company_name;

Output:

Overview: In this instance, the SQL SUBSTRING() function retrieved solely the local number while eliminating the other numbers from the dataset.

How to Implement SQL SUBSTRING in Queries?

The SQL SUBSTRING() function enables you to extract a specific segment of a string based on the starting position and the length. It is primarily utilized in queries for parsing, sanitizing, and assessing text information.

Let’s initiate by forming a sample data set that we will utilize to illustrate how the SQL SUBSTRING() function operates across various queries.

CREATE TABLE Employees (
    EmployeeID INT,
    FullName VARCHAR(20),
    DepartmentCode VARCHAR(10),
    PhoneNumber VARCHAR(20)
);
INSERT INTO Employees (EmployeeID, FullName, DepartmentCode, PhoneNumber)
VALUES
(1, 'Karan', 'HR-010', '+1-789-12343457'),
(2, 'Baskar', 'FIN-633', '+1-234-3254634'),
(3, 'Chowdary', 'MKT-459', '+1-877-346457'),
(4, 'Dhanush', 'SEC-452', '+1-443-354654776'),
(5, 'Madhan', 'FIN-234', '+1-344-45756786');
SELECT * FROM Employees;

Result:

This is a sample output displayed after the table creation, showcasing the data saved within it.

1. Utilizing SQL SUBSTRING with String Constants

You can apply SUBSTRING() directly on a string to extract the required portion of the data from that string.

Illustration:

SELECT SUBSTRING('Machine Learning', 9, 5) AS Extracted;

Result:

Overview: In this case, the SUBSTRING() extracted the string starting from position 9 up to a length of 5 characters.

2. Applying SQL SUBSTRING with Column Data

SUBSTRING() can be employed on column values to extract elements such as names, codes, and numbers from the text stored in a table.

Illustration:

SELECT 
    FullName, 
    SUBSTRING(PhoneNumber, 9, 7) AS LocalNumber
FROM Employees;

Result:

Overview: In this scenario, the SUBSTRING() function retrieved only the local number from the complete phone number associated with the employees.

3. Employing SQL SUBSTRING in WHEREClause

A SUBSTRING() function can be paired with a WHERE Clause to filter strings based on certain conditions. This is especially beneficial when the data consists of codes or structured text.

Illustration:

SELECT *
FROM Employees
WHERE SUBSTRING(DepartmentCode, 1, 3) = 'FIN';

Result:

Overview: In this instance, the WHERE clause retrieved details of employees whose department begins with FINANCE, namely “FIN.”

4. Integrating SQL SUBSTRING in JOIN Conditions

The JOIN clause can be integrated with SUBSTRING() to connect two tables based on corresponding segments of their columns. Utilizing the following example, join the employees table to achieve the expected outcome.

Illustration:

CREATE TABLE Departments (
    DeptPrefix VARCHAR(3),
    DeptName VARCHAR(50)
);
INSERT INTO Departments (DeptPrefix, DeptName)
VALUES
('HR', 'Human Resources'),
('SEC', 'Secretary'),
('MKT', 'Marketing'),
('FIN', 'Finance');
SELECT 
    E.FullName, 
    D.DeptName
FROM Employees E
JOIN Departments D
    ON SUBSTRING(E.DepartmentCode, 1, 3) = D.DeptPrefix;

Result:

Overview: In this example, the JOIN clause combined two tables concerning their departments. The employee “Karan” is omitted because the department prefix “HR” may not align due to data discrepancies or formatting problems in the JOIN condition.

Advanced Applications of SQL SUBSTRING

The SUBSTRING() function in SQL can also be utilized in advanced use cases, such as dynamic joins, conditional filtering, and targeted updates founded on partial string matches.

1. Utilizing SQL SUBSTRING on Table Columns

The SQL SUBSTRING() extracts a segment of a string from a table column with the aid of the starting position and length. It is beneficial when you wish to structure text like ID, date, and codes within a broader string.

Illustration:

SELECT FullName, DepartmentCode, SUBSTRING(DepartmentCode, 1, 3) AS DeptPrefix FROM Employees;

Result:

Overview: In this example, the SQL SUBSTRING() function extracts the name and department code by initiating from position 1 and taking the first 3 characters as a prefix.

2. SQL SUBSTRING for Modifying Column Values

We can utilize SQL SUBSTRING for altering column values using the UPDATE statement to manipulate the data in a table.

Illustration: If you wish to modify or update the contact number of an employee to safeguard their details, you can apply the UPDATE statement.

“`

UPDATE Employees
SET PhoneNumber = CONCAT(SUBSTRING(PhoneNumber, 1, 8), 'XXXXXXX')
WHERE EmployeeID = 1;
SELECT * FROM Employees;

Result:

Clarification: In this instance, the phone number of an employee, “Karan”, has been modified to “XXXXX” to conceal their contact number using an UPDATE command along with the SUBSTRING() function. 

SQL SUBSTRING vs Other String Operations

Function	Efficiency Rate	Optimal Use Scenario
SUBSTRING()	Moderate, as it’s a bit slower in WHERE or JOIN scenarios	Extracting a particular portion of strings regardless of its placement.
LEFT()	High, as it’s fine-tuned for rapid filtering tasks	Returning characters from the start.
RIGHT()	High, as it operates efficiently like LEFT()	Retrieving characters from the finish.
CHARINDEX()	Moderate, as it’s swift on smaller strings but slower on lengthy text	Quickly identifying character positions for preliminary checks.
LEN()	Very High, as it’s swift and effective for most scenarios	Determining string length for checks or segments.

These are the distinctions between SQL SUBSTRING() and alternative String functions. Nevertheless, refrain from utilizing these functions in a WHERE or JOIN Clause whenever feasible to prevent performance issues on extensive datasets. 

Frequent SQL SUBSTRING Mistakes and How to Prevent Them

There are certain mistakes encountered while using SQL SUBSTRING(). Below are common errors along with solutions on how to circumvent them.

1. Improper Usage of Datatype

Error: This will trigger an error if the datatype contains integers instead of strings.
To Prevent: Initially, convert the integer value to a VARCHAR type by utilizing CAST(column AS VARCHAR). This allows for string operations such as SUBSTRING() on the converted data.

2. Incorrect Starting Position or Length

Error: If the initial position designated in SUBSTRING() exceeds the string’s length, SQL Server, MySQL, and PostgreSQL return an empty string instead of an error, while Oracle raises an error.
To Prevent: ensure you utilize the LEN() function to verify the length prior to applying SUBSTRING().

3. Using NULL Without Proper Handling

Error: If NULL is included in the text segment, then the SUBSTRING will yield NULL.
To Avoid: Employ the COALESCE function to safely handle NULL values in SUBSTRING().

4. Assuming SQL Utilizes 0-Based Indexing

Error: SQL operates on a 1-based index, so do not assume zero-based indexing like in various programming languages.
To Prevent: Always ensure the counting starts from a designated value.

5. Unexpected Outcomes in JOINs

Error: An error will surface if the two joined tables deliver different outputs.
To Evade: Confirm that both tables are compatible. If not, employ the TRIM() or CAST() functions when required.

Optimal Practices for Using SQL SUBSTRING

To effectively and efficiently apply the SQL SUBSTRING() function, consider these best practices:

1. Always Validate Your Input: Utilize functions like LEN() or CHARINDEX() to ensure extraction won’t cause errors.

2. Combine the SUBSTRING() function with other SQL string functions: Incorporating SUBSTRING() with dynamic content is effective when paired with CHARINDEX(), LEFT(), or RIGHT() during processing.

3. Use with caution in WHERE and JOIN clauses: Avoid using SUBSTRING() in WHERE or JOIN clauses on extensive tables as it may degrade query performance.

4. Clearly Alias Your Output: Employ clear aliases for your result columns, such as AS DeptPrefix, to enhance output clarity.

5. Maintain your code’s cleanliness/readability: To create cleaner and easier-to-maintain code, divide extensive or complex code into smaller, manageable segments.

Conclusion

Understanding how to utilize the SQL SUBSTRING() function and its operation is vital for effectively manipulating strings within database queries. It is particularly useful for extracting, reformatting, and modifying text data, but it’s crucial to recognize the limitations and best practices for employing SQL SUBSTRING() successfully. Begin by experimenting with simple examples of the SQL SUBSTRING() function. Once you’re comfortable, pair it with other string functions such as LEFT(), RIGHT(), and CHARINDEX() to enhance your SQL query writing abilities. This article has provided insights into the SQL SUBSTRING() function, its utilization, common mistakes, and methods to circumvent them.

Advance your skills by enrolling in the SQL Course today and obtain hands-on experience. Additionally, prepare for job interviews with SQL Interview Questions devised by industry professionals.

SQL SUBSTRING – FAQs

The post SQL SUBSTRING first appeared on Intellipaat Blog.

“`