SQL Server String SUBSTRING() Function

SQL Server SUBSTRING() Function

The SQL Server SUBSTRING() function is used to extract a portion of a string starting at a specified position and for a specified length. This function is useful for retrieving specific parts of a string.

Syntax

SELECT SUBSTRING(string, start_position, length);

The SUBSTRING() function takes three arguments:

  • string: The string from which to extract the substring.
  • start_position: The position in the string where the extraction begins (1-based index).
  • length: The number of characters to extract from the string.

Example SQL Server SUBSTRING() Function Queries

Let's look at some examples of SQL Server SUBSTRING() function queries:

1. Basic SUBSTRING() Example

SELECT SUBSTRING('Hello World', 7, 5) AS result;

This query extracts 5 characters starting from the 7th position in the string 'Hello World'. The result will be:

result
------
World

2. SUBSTRING() with a Column

SELECT first_name, SUBSTRING(first_name, 1, 3) AS short_name
FROM employees;

This query extracts the first 3 characters from the first_name column for each employee. The result will show the first_name and the extracted substring as short_name.

3. SUBSTRING() with a Variable

DECLARE @myString VARCHAR(50);
SET @myString = 'SQL Server';
SELECT SUBSTRING(@myString, 5, 6) AS result;

This query uses a variable to store a string and then extracts 6 characters starting from the 5th position. The result will be:

result
------
Server

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the SUBSTRING() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    description VARCHAR(50)
);

In this example, we create a table named example_table with columns for id and description.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, description) VALUES (1, 'Apple Pie');
INSERT INTO example_table (id, description) VALUES (2, 'Banana Bread');
INSERT INTO example_table (id, description) VALUES (3, 'Cherry Tart');

Here, we insert data into the example_table.

Step 3: Using the SUBSTRING() Function

This step involves using the SUBSTRING() function to extract substrings from the description column.

SELECT id, description, SUBSTRING(description, 1, 5) AS short_description
FROM example_table;

This query retrieves the id, description, and the first 5 characters of the description column for each row in the example_table. The result will be:

id  description  short_description
--- ------------ -----------------
1   Apple Pie    Apple
2   Banana Bread Ban
3   Cherry Tart  Cherry

Conclusion

The SQL Server SUBSTRING() function is a powerful tool for extracting a portion of a string starting at a specified position and for a specified length. Understanding how to use the SUBSTRING() function and its syntax is essential for effective string manipulation and data processing in SQL Server.