SUBSTRING Command in SQL: A Primer

Similar to my article regarding the SQL Server STUFF command, today we will be examining the SUBSTRING SQL server command. It is similar to the STUFF command in regards to the parameters it accepts.

In the vast landscape of data manipulation, one function that consistently proves its worth is the ‘SUBSTRING’ in SQL Server. As a seasoned tech writer with a deep understanding of SQL Server development, I’m thrilled to delve into this powerful tool, shedding light on its practicality and versatility.

In this blog post, we will embark on a journey to explore the ‘SUBSTRING’ function, a cornerstone of string manipulation in SQL Server. We will dissect its syntax, delve into illustrative examples, and examine real-world use cases. Whether you’re a seasoned SQL developer or a beginner looking to expand your toolkit, this exploration of ‘SUBSTRING’ promises to enhance your understanding and proficiency in SQL Server.

So, buckle up as we dive into the world of ‘SUBSTRING’ in SQL Server, a function that stands as a testament to the power and flexibility of SQL as a language for managing and manipulating data.

Exploring SQL Substring()

The SUBSTRING() function is a built-in function in SQL Server that allows you to extract a sequence of characters from a string. It’s a highly versatile tool that plays a crucial role in data manipulation and analysis, particularly when dealing with text-based data fields.

Here’s the syntax for the SUBSTRING() function:

SUBSTRING ( STRING_VALUE , STARTING_POSITION, LENGTH)

The SUBSTRING() function takes three arguments:

1. Expression: This is the string from which you want to extract a substring. It can be a character, binary, text, ntext, image, or a column of these data types.
2. Start: This is an integer value that specifies where the substring should start. The count begins at 1, meaning if you pass 1 as the start value, the function will start from the first character of the specified string.
3. Length: This is another integer value that determines the number of characters to be extracted from the start position. If the length is more than the number of characters remaining in the string (starting from the ‘start’ position), the function will return all the remaining characters.

The SUBSTRING command is prevalent in most major standard databases like SQL Server, MySQL, Oracle, and Postgres.

Let’s explore the SUBSTRING function with several examples.

Example 1: Simple SUBSTRING in SQL Server

In this first example, a string value of “ABC” will be passed to the SUBSTRING function. Each call will have the starting position argument incremented by 1. The length argument will stay 1 for each call.

SELECT SUBSTRING('ABC',2,1);

Output

B

From these examples we can see the general functionality of the SUBSTRING function, each increment in the starting position shifts the letter returned. In execution #1 the value is A, #2 the value is B and in #3 the value is C.

An interesting feature of the SUBSTRING function is that the length parameter is non-breaking if it exceeds the length of the value you’re parsing. For example, the value “1000” has a length of 4 digits. If parsed with the following query specifying a length of 6, SUBSTRING returns the original string.

SELECT SUBSTRING('1000',1,6);

Output

1000

Example 2: Parsing Data with the SUBSTRING Function

For the next example, let’s assume that we have a database for keeping data about used cars. However, instead of using a multi-table relational data model, the database contains a primary key and an SKU or stock-keeping unit only. This SKU can be used to find multiple fields around the features of a car by using the following key:

TOY-CEL-R-1990-150
-------------------------------------------------------
MAKE-MODEL-COLOR-YEAR-MILEAGE

*In these examples mileage is represented at a scale of 1/1000, i.e. 100 = 100,000

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'CODESIGHT_DEMOS')
BEGIN
  CREATE DATABASE CODESIGHT_DEMOS;
END;
GO

USE CODESIGHT_DEMOS;

IF OBJECT_ID('USEDCARS') IS NOT NULL DROP TABLE USEDCARS
CREATE TABLE USEDCARS
(
ID INT IDENTITY(1,1) PRIMARY KEY,
CAR_SKU VARCHAR(30)
)

--MAKE--MODEL--COLOR--YEAR--MILEAGE *1000
INSERT INTO USEDCARS
VALUES('TOY-CEL-R-1990-150')

INSERT INTO USEDCARS
VALUES('JEP-WRG-W-2019-15')

INSERT INTO USEDCARS
VALUES('FRD-ESC-G-1998-80')

SELECT * FROM USEDCARS

The output of the used car table is:

ID	CAR_SKU
1	TOY-CEL-R-1990-150
2	JEP-WRG-W-2019-15
3	FRD-ESC-G-1998-80

Using the SUBSTRING function, these values can be parsed by assigning the correct starting position and length parameters to the CAR_SKU field.

SELECT SUBSTRING(CAR_SKU,1,3) AS MAKE,
SUBSTRING(CAR_SKU,5,3) AS MODEL,
SUBSTRING(CAR_SKU,9,1) AS COLOR,
SUBSTRING(CAR_SKU,11,4) AS YEAR,
SUBSTRING(CAR_SKU,16,3) * 1000 AS MILEAGE
FROM USEDCARS;

MAKE	MODEL	COLOR	YEAR	MILEAGE
TOY	CEL	R	1990	150000
JEP	WRG	W	2019	15000
FRD	ESC	G	1998	80000

Example 3: Abstracting the SUBSTRING SQL Logic to Views

Using the SQL SUBSTRING function, we were able to parse out the various features of each vehicle in our database. Let’s take this 1 step farther and translate that output into more meaningful data by building a view on the SUBSTRING query.

In a relational data model, this will be achieved by using joins to incorporate the CASE WHEN statement to translate the values.

CREATE VIEW  VW_CAR_INVENTORY

AS

SELECT 

CASE WHEN SUBSTRING(CAR_SKU,1,3) = 'TOY' THEN 'TOYOTA'
     WHEN SUBSTRING(CAR_SKU,1,3) = 'JEP' THEN 'JEEP'
	 WHEN SUBSTRING(CAR_SKU,1,3) = 'FRD' THEN 'FORD'
	 END AS MAKE,

CASE WHEN SUBSTRING(CAR_SKU,5,3) = 'CEL' THEN 'CELICA'
     WHEN SUBSTRING(CAR_SKU,5,3) = 'WRG' THEN 'WRANGLER'
	 WHEN SUBSTRING(CAR_SKU,5,3) = 'ESC' THEN 'ESCAPE'
    END AS MODEL,

CASE WHEN SUBSTRING(CAR_SKU,9,1) = 'R' THEN 'RED'
     WHEN SUBSTRING(CAR_SKU,9,1) = 'W' THEN 'WHITE'
	 WHEN SUBSTRING(CAR_SKU,9,1) = 'G' THEN 'GREEN'
    END AS COLOR,

SUBSTRING(CAR_SKU,11,4) AS YEAR,

SUBSTRING(CAR_SKU,16,3) * 1000 AS MILEAGE

FROM USEDCARS

MAKE	MODEL	COLOR	YEAR	MILEAGE
TOYOTA	CELICA	RED	1990	150000
JEEP	WRANGLER	WHITE	2019	15000
FORD	ESCAPE	GREEN	1998	80000

Example 4: SUBSTRING Alternatives for Integer Values

The SUBSTRING function is explicitly for string values, so the following query attempting to parse an integer value with SUBSTRING will fail.

SELECT SUBSTRING(1000,1,1); 

--Msg 8116, Level 16, State 1, Line 78 Argument data type int is invalid for argument 1 of substring function.

The alternative when parsing integer values is the LEFT or RIGHT function, although this approach loses some of the flexibility presented with the SUBSTRING function.

SELECT LEFT(10000,1);

Output

1

SELECT RIGHT(1000,1) ;

Output

0

Situationally you could also explicitly cast the integer value as a string and substring the casted value:

SELECT (SUBSTRING(CAST(1000 AS VARCHAR(4)),1,1)) AS CASTED_SUBSTRING

Output

1

Summary

This article covered the parameters required to use the SUBSTRING function which is a strong argument for a starting position and a length. With a non-relation data model, we used the SUBSTRING method to extract the car features from a 1 field table and construct a view with some transformation logic built into it.

The SUBSTRING method is a great function to know when performing ETL or parsing the data inside a database. It can be used to extract meaningful key data points from the already existing data inside of your database.

Tags: sql, sql server, sql substring Last modified: June 12, 2023