Figure 1: GENERATE_SERIES Usage #1 - Expand ZIP+ZIP4 Ranges to Individual ZIP+4 codes
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.
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'.
GENERATE_SERIES ( start , stop [ , step ] )
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 |
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.
Here are sample uses of the `GENERATE_SERIES` relational operator:
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 |
Figure 1: GENERATE_SERIES Usage #1 - Expand ZIP+ZIP4 Ranges to Individual ZIP+4 codes
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.
Figure 2: GENERATE_SERIES Usage #2 - Find the Unique Character (or number) from a String (LeetCode 136)
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.
Figure 3: GENERATE_SERIES Usage #3 - Reverse a String
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,
Figure 4: GENERATE_SERIES Usage #4 - INITCAP (String Capitalization) Function
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 | |----------------------| | tk45BBEJtXKJ17zbHJ4T |
Figure 5a: GENERATE_SERIES Usage #5 - Generate a Random Password (Alphanumeric)
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 | |----------------------| | o2Vqn1Q2oafR#PBcICTV |
Figure 5b: GENERATE_SERIES Usage #5 - Generate a Random Password (Alphanumeric and Special Characters)