Skip to content

GENERATE_SERIES Relational Operator

The GENERATE_SERIES relational operator generates a series of numbers within a given interval wherein the interval and the step between series values are defined by the user.

Compatibility level 160

The GENERATE_SERIES relational operator requires the compatibility level to be at least 160. When the compatibility level is less than 160, the Database Engine is unable to find the GENERATE_SERIES function and will generate an error as illustrated below:

SELECT compatibility_level
FROM sys.databases WHERE name = 'SQLServerHelper';
compatibility_level
110
SELECT value FROM GENERATE_SERIES(1, 5);
Query 1 ERROR at Line 1: : Msg: 208, Line 1, State: 1, Level: 16
Invalid object name 'GENERATE_SERIES'.

Syntax

GENERATE_SERIES ( start , stop [ , step ] )

Arguments

start

The first value in the interval. start is specified as a variable, a literal, or a scalar expression of type tinyint, smallint, int, bigint, decimal, or numeric.

stop

The last value in the interval. stop is specified as a variable, a literal, or a scalar expression of type tinyint, smallint, int, bigint, decimal, or numeric. The series stops once the last generated step value exceeds the stop value.

The data type for stop must match the data type for start, otherwise an error will be generated such as the following:

SELECT value FROM GENERATE_SERIES(1, 5.5);
Query 1 ERROR at Line 1: : Msg: 5373, Line 1, State: 1, Level: 16
All the input parameters should be of the same type. Supported types are tinyint, smallint, int, bigint, decimal and numeric.
Msg: 206, Line 1, State: 2, Level: 16
Operand type clash: int is incompatible with void type
Msg: 206, Line 1, State: 2, Level: 16
Operand type clash: numeric is incompatible with void type

[ step ]

Indicates the number of values to increment or decrement between steps in the series. step is an expression of type tinyint, smallint, int, bigint, decimal, or numeric. step can be either negative or positive, but can't be zero (0). Passing a value of zero (0) to the step argument generates the following error:

SELECT value FROM GENERATE_SERIES(1, 5, 0);
Query 1 ERROR at Line 1: : Msg: 4199, Line 1, State: 1, Level: 16
Argument value 0 is invalid for argument 3 of generate_series function.

The step argument is optional. The default value for step is 1 if start is less than stop, otherwise, the default value is -1 if start is greater than stop. To illustrate:

SELECT value FROM GENERATE_SERIES(1.5, 5.5);
value
1.5
2.5
3.5
4.5
5.5

If start is less than stop and a negative value is specified for step, or if start is greater than stop and a positive value is specified for step, an empty result set is returned. To illustrate:

SELECT COUNT(value) AS [value_count] FROM GENERATE_SERIES(1, 5, -1);
value_count
0
SELECT COUNT(value) AS [value_count] FROM GENERATE_SERIES(5, 1, 1);
value_count
0

Return Types

Returns a single-column table containing a sequence of values in which each differs from the preceding by step. The name of the column is value. The output is the same type as start and stop.

Sample Uses of the GENERATE_SERIES Relational Operator

Here are sample uses of the GENERATE_SERIES relational operator:

Usage #1: Expand ZIP+ZIP4 Ranges to Individual ZIP+4 codes

A ZIP Code (an acronym for Zone Improvement Plan) is a system of postal codes used by the United States Postal Service (USPS). The ZIP code format consists of five digits that indicate a specific delivery area. The first three digits indicates the sectional center or large city while the last two digits indicates the post office facility or delivery area.

An extended code was introduced by the USPS called the ZIP+4 code system. The ZIP+4 code system included the five digits of the ZIP code followed by a hyphen and four digits. The goal is to narrow down a location within a delivery ZIP code area, possibly to a specific building, floor, or apartment suite.

A table containing a list of city, state and ZIP codes together with the range of ZIP+4 associated with the city and state can be expanded to the individual ZIP+4 (or ZIP9) using the GENERATE_SERIES relational operator.

Using the following sample list of city, state and ZIP codes with a range of ZIP4 values:

WITH ZIPs AS (
  SELECT *
  FROM (VALUES ('Chicago', 'IL', '60614', 101, 120),
               ('New York', 'NY', '10001', 10, 20),
               ('Seattle', 'WA', '98109', 1001, 1010))
  AS ZIPs ( [City], [State], [ZIP], [ZIP4_Start], [ZIP4_End])
)
SELECT * FROM ZIPs;
City State ZIP ZIP4_Start ZIP4_End
Chicago IL 60614 101 120
New York NY 10001 10 20
Seattle WA 98109 1001 1010

This table can be expanded to the individual ZIP+4 codes using the GENERATE_SERIES relational operator with a little help from the CROSS APPLY operator. The syntax of the CROSS APPLY is as follows:

left_table_source CROSS APPLY right_table_source

This specifies that the right_table_source of the CROSS APPLY operator is evaluated against every row of the left_table_source. This functionality is useful when the right_table_source contains a table-valued function that takes column values from the left_table_source as one of its arguments, as is the case with the query below:

WITH ZIPs AS (
  SELECT *
  FROM (VALUES ('Chicago', 'IL', '60614', 101, 120),
               ('New York', 'NY', '10001', 10, 20),
               ('Seattle', 'WA', '98109', 1001, 1010))
  AS ZIPs ( [City], [State], [ZIP], [ZIP4_Start], [ZIP4_End])
)
SELECT [City], [State], [ZIP], RIGHT('0000' + CAST(value AS VARCHAR(4)), 4) AS [ZIP4],
       CONCAT_WS('-', [ZIP], RIGHT('0000' + CAST(value AS VARCHAR(4)), 4)) AS [ZIP9]
FROM ZIPs CROSS APPLY GENERATE_SERIES([ZIP4_Start], [ZIP4_End], 1);
City State ZIP ZIP4 ZIP9
Chicago IL 60614 0101 60614-0101
Chicago IL 60614 0102 60614-0102
Chicago IL 60614 0103 60614-0103
Chicago IL 60614 0104 60614-0104
Chicago IL 60614 0105 60614-0105
Chicago IL 60614 0106 60614-0106
Chicago IL 60614 0107 60614-0107
Chicago IL 60614 0108 60614-0108
Chicago IL 60614 0109 60614-0109
Chicago IL 60614 0110 60614-0110
Chicago IL 60614 0111 60614-0111
Chicago IL 60614 0112 60614-0112
Chicago IL 60614 0113 60614-0113
Chicago IL 60614 0114 60614-0114
Chicago IL 60614 0115 60614-0115
Chicago IL 60614 0116 60614-0116
Chicago IL 60614 0117 60614-0117
Chicago IL 60614 0118 60614-0118
Chicago IL 60614 0119 60614-0119
Chicago IL 60614 0120 60614-0120
New York NY 10001 0010 10001-0010
New York NY 10001 0011 10001-0011
New York NY 10001 0012 10001-0012
New York NY 10001 0013 10001-0013
New York NY 10001 0014 10001-0014
New York NY 10001 0015 10001-0015
New York NY 10001 0016 10001-0016
New York NY 10001 0017 10001-0017
New York NY 10001 0018 10001-0018
New York NY 10001 0019 10001-0019
New York NY 10001 0020 10001-0020
Seattle WA 98109 1001 98109-1001
Seattle WA 98109 1002 98109-1002
Seattle WA 98109 1003 98109-1003
Seattle WA 98109 1004 98109-1004
Seattle WA 98109 1005 98109-1005
Seattle WA 98109 1006 98109-1006
Seattle WA 98109 1007 98109-1007
Seattle WA 98109 1008 98109-1008
Seattle WA 98109 1009 98109-1009
Seattle WA 98109 1010 98109-1010

Usage #2: Find the Unique Character (or number) from a String (LeetCode 136)

LeetCode 136: Single Number has the following requirements:

Given a non-empty array of integers nums, every element appears twice except for one. Find that single one.

This question is not a database question but a question for a programming language such as Python or Javascript. But this question can also be answered using SQL Server Transact-SQL. This question can also be extended not just to a list of integers but also to a list of characters.

To identify the character in a string that appears only once, the exclusive OR bitwise operator (or XOR (^) for short) will be used. The ^ bitwise operator performs a bitwise logical exclusive OR between two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if either (but not both) bits (for the current bit being resolved) in the input expressions have a value of 1. If both bits are 0 or both bits are 1, the bit in the result is cleared to a value of 0. So if a number is XORed to itself, the output will be 0.

To identify the character in a string that appears only once, the ASCII value of each character will be XOR'ed and the resulting ASCII value after all characters have been processed will be the character that appears only once. To go through each character in a string, the GENERATE_SERIES relational operator will be used as shown in the following query:

DECLARE @Input VARCHAR(100) = 'RACECAR'
DECLARE @Output INT = 0

SELECT @Output = @Output ^ ASCII(SUBSTRING(@Input, value, 1))
FROM GENERATE_SERIES(1, LEN(@Input))

SELECT CHAR(@Output) AS [Output];
Output
E

Passing the length of the input string (LEN(@Input)) as the second argument to the GENERATE_SERIES relational operator, this generates a list of numbers from 1 to that number. The query then gets the character at that position and gets the ASCII value of that character (ASCII(SUBSTRING(@Input, value, 1))). The ASCII value is then XORed with the @Output variable, which contains the XOR value of all the previous characters. The resulting @Output value contains the ASCII value of the character that appears only once in the input string. The CHAR function is then used to get the character associated with the ASCII value.

Usage #3: Reverse a String

The REVERSE string function, a built-in function in SQL Server Transact-SQL, returns the reverse of a string value.

SELECT REVERSE('ABDEFGHIJKLMNOPQRSTUVWXYZ') AS [Output];
Output
ZYXWVUTSRQPONMLKJIHGFEDBA

This function can be simulated with the use of the GENERATE_SERIES and with a little help from the STRING_AGG string function. The STRING_AGG string function concatenates the values of string expressions and places separator values between them. The separator isn't added at the end of string.

As a simple example, here's how to use the STRING_AGG string function to concatenate a list of words:

SELECT STRING_AGG([Word], ' ') AS [Output]
FROM (VALUES ('The'), ('quick'), ('brown'), ('fox'))
  AS [Words] ([Word])
Output
The quick brown fox

To reverse the string ABCDEFGHIJKLMNOPQRSTUVWXYZ without using the REVERSE string function, the query will look as follows:

DECLARE @Input VARCHAR(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

SELECT STRING_AGG(SUBSTRING(@Input, value, 1), '') AS [Output]
FROM GENERATE_SERIES(LEN(@Input), 1);
Output
ZYXWVUTSRQPONMLKJIHGFEDCBA

What the query does is to create a list of numbers starting from the length of the input string (LEN(@Input)) all the way down to 1 (GENERATE_SERIES(LEN(@Input), 1)). Then for each number from the list, the query retrieves the character in that position from the @Input string (SUBSTRING(@Input, value, 1)) and concatenates them together (STRING_AGG(SUBSTRING(@Input, value, 1), '') AS [Output]) to generate the desired output.

Usage #4: INITCAP (String Capitalization) Function

The INITCAP string function of Oracle changes the first character of each word in a string to uppercase. The remaining characters are made lowercase. Words are delimited by white space or characters that are not alphanumeric.

Given the title of the following books in the Harry Potter series, which are all in uppercase:

WITH [Books] AS (
  SELECT *
  FROM (VALUES (1, 'HARRY POTTER AND THE SORCERER''S STONE'),
               (2, 'HARRY POTTER AND CHAMBER OF SECRETS'),
               (3, 'HARRY POTTER AND THE PRISONER OF AZKABAN'),
               (4, 'HARRY POTTER AND THE GOBLET OF FIRE'),
               (5, 'HARRY POTTER AND THE ORDER OF THE PHOENIX'),
               (6, 'HARRY POTTER AND THE HALF-BLOOD PRINCE'),
               (7, 'HARRY POTTER AND THE DEATHLY HALLOWS'))
  AS [Books] ( [BookID], [Title])
)
SELECT * FROM [Books];
BookID Title
1 HARRY POTTER AND THE SORCERER'S STONE
2 HARRY POTTER AND CHAMBER OF SECRETS
3 HARRY POTTER AND THE PRISONER OF AZKABAN
4 HARRY POTTER AND THE GOBLET OF FIRE
5 HARRY POTTER AND THE ORDER OF THE PHOENIX
6 HARRY POTTER AND THE HALF-BLOOD PRINCE
7 HARRY POTTER AND THE DEATHLY HALLOWS

The titles can be made more readable by capitalizing only the first character of each word in the title. This can be achieved with the help of the GENERATE_SERIES relational operator as shown in the following query:

WITH [Books] AS (
  SELECT *
  FROM (VALUES (1, 'HARRY POTTER AND THE SORCERER''S STONE'),
               (2, 'HARRY POTTER AND CHAMBER OF SECRETS'),
               (3, 'HARRY POTTER AND THE PRISONER OF AZKABAN'),
               (4, 'HARRY POTTER AND THE GOBLET OF FIRE'),
               (5, 'HARRY POTTER AND THE ORDER OF THE PHOENIX'),
               (6, 'HARRY POTTER AND THE HALF-BLOOD PRINCE'),
               (7, 'HARRY POTTER AND THE DEATHLY HALLOWS'))
  AS [Books] ( [BookID], [Title])
)
SELECT [BookID], STRING_AGG(CASE WHEN value = 1
                       THEN UPPER(SUBSTRING([Title], value, 1))
                       WHEN SUBSTRING([Title], value - 1, 1) IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(') AND (SUBSTRING([Title], value - 1, 1) != '''' OR SUBSTRING([Title], value, 1) != 'S')
                       THEN UPPER(SUBSTRING([Title], value, 1))
                       ELSE LOWER(SUBSTRING([Title], value, 1)) END, '') AS [Output]
FROM [Books] CROSS APPLY GENERATE_SERIES(1, LEN([Title]))
GROUP BY [BookID];
BookID Output
1 Harry Potter And The Sorcerer's Stone
2 Harry Potter And Chamber Of Secrets
3 Harry Potter And The Prisoner Of Azkaban
4 Harry Potter And The Goblet Of Fire
5 Harry Potter And The Order Of The Phoenix
6 Harry Potter And The Half-Blood Prince
7 Harry Potter And The Deathly Hallows

A character is capitalized,

  • if it is the first character of the string, or
  • if the character previous to the current character is one of the non-alphanumeric characters listed in the query except if the current character is the letter S and the previous character is the single quotes (').

Usage #5: Generate a Random Password

Given a list of characters, a random character can be selected from it using the following query:

DECLARE @ValidChars VARCHAR(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';

SELECT SUBSTRING(@ValidChars, (ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 
                 LEN(@ValidChars)) + 1, 1) AS [Output];

The NEWID() system function creates a unique value of type uniqueidentifier. The output of the NEWID() function then gets converted to an INT data type by first casting it to a VARBINARY data type (CAST(CAST(NEWID() AS VARBINARY) AS INT)). The generated INT value can be a negative number so it is converted to a positive value using the ABS (absolute value) mathematical function. To make sure that the generated INT value is within the length of the list of characters where the random character is to be selected from, the modulo (%) operator is used against it using the length of the list of characters as the divisor. The output of the modulo operation will be a number between 0 and one less than the length of the list of characters. 1 is added to the output so that the generated number is between 1 and the length of the list of characters. Then the character at the position specified by the output is the random character.

Using the query above in generating a random character from a list of valid characters that can be used for a password, a password can then randomly be generated by adding the GENERATE_SERIES relational operator in the query as well as by adding the STRING_AGG string function to put together the random characters generated. The resulting query to generate a random password is as follows:

DECLARE @ValidChars VARCHAR(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';

SELECT STRING_AGG(SUBSTRING(@ValidChars, (ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 
                  LEN(@ValidChars)) + 1, 1), '') [Password]
FROM GENERATE_SERIES(1, 20) [PasswordLength];
Password
DGMRHmHj4r58LS13dhi9

The generated random password above only consists of alphanumeric characters. If a special character needs to be included, the list of valid characters can be extended to include the special characters:

DECLARE @ValidChars VARCHAR(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()';

SELECT STRING_AGG(SUBSTRING(@ValidChars, (ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 
                  LEN(@ValidChars)) + 1, 1), '') [Password]
FROM GENERATE_SERIES(1, 20) [PasswordLength];
Password
Oopj4j5S^6Z$s4l8a%cw