Removing Repeated Characters From a String With GENERATE_SERIES

Cleaning a code such as AAB--12 can mean keeping only the first occurrence of each character, producing AB-12. Removing repeated characters used to invite a WHILE loop in a scalar function. SQL Server 2022 can split positions with GENERATE_SERIES and rebuild the first occurrences as a set.

Define What Counts as Repeated Characters

The example uses character equality under the active collation. In a case-insensitive collation, A and a compare equal; in a binary collation, they differ. Leading, internal, and trailing spaces also need a rule. The code below is for short varchar codes and uses LEN, which does not count trailing spaces. State those constraints before removing repeated characters from free-form text or Unicode strings.

I start with a few examples written as input and expected output. AAB--12 becomes AB-12; BANANA becomes BAN. What should happen to Aa in your system? That answer determines the collation used in the partition.

Split Positions With GENERATE_SERIES

GENERATE_SERIES is available in SQL Server 2022 with database compatibility level 160. It returns integer positions from 1 to LEN of the code. SUBSTRING reads one character at each position. ROW_NUMBER partitions by character and orders by position; row number 1 is the first occurrence to keep.

DECLARE @code varchar(100) = 'AAB--12';
WITH characters AS
(
    SELECT g.value AS pos,
           SUBSTRING(@code, g.value, 1) AS ch
    FROM GENERATE_SERIES(1, LEN(@code)) AS g
), ranked AS
(
    SELECT pos, ch,
           ROW_NUMBER() OVER
             (PARTITION BY ch ORDER BY pos) AS occurrence
    FROM characters
)
SELECT STRING_AGG(CAST(ch AS varchar(max)), '')
       WITHIN GROUP (ORDER BY pos) AS cleaned_code
FROM ranked
WHERE occurrence = 1;

The output is AB-12. For an empty input, GENERATE_SERIES(1, 0) counts down and returns 1 and 0, so the query returns an empty string. A NULL input returns no positions and a NULL result; use COALESCE if the required result is an empty string. Keep the first occurrence position so STRING_AGG can restore the original order.

Remove Repeated Characters Across a Table Without a Loop

CROSS APPLY lets the same logic run per source row. Use a stable row key to group characters from each code. The query below assumes dbo.Codes has CodeID and CodeValue; substitute the real table. Casting to a max type inside STRING_AGG avoids an 8,000-byte aggregation limit for longer inputs.

SELECT c.CodeID,
       COALESCE(x.cleaned_code, '') AS cleaned_code
FROM dbo.Codes AS c
OUTER APPLY
(
    SELECT STRING_AGG(CAST(r.ch AS varchar(max)), '')
           WITHIN GROUP (ORDER BY r.pos) AS cleaned_code
    FROM
    (
        SELECT g.value AS pos,
               SUBSTRING(c.CodeValue, g.value, 1) AS ch,
               ROW_NUMBER() OVER
               (
                   PARTITION BY SUBSTRING(c.CodeValue, g.value, 1)
                   ORDER BY g.value
               ) AS occurrence
        FROM GENERATE_SERIES(1, LEN(c.CodeValue)) AS g
    ) AS r
    WHERE r.occurrence = 1
) AS x;

For many codes, generating one row per character can become a large intermediate set. It avoids a scalar WHILE loop but still has real CPU and memory cost. Measure the average and maximum code length, not only the number of source rows.

Use a Numbers Table on SQL Server 2019

SQL Server 2019 does not have GENERATE_SERIES. Create a permanent Numbers table containing 1 through at least the maximum code length, with n as its primary key. The query below shows the position rows it produces for each code. Inside the APPLY, the same join to dbo.Numbers replaces GENERATE_SERIES, and the ranking and aggregation logic stays the same.

-- SQL Server 2019: dbo.Numbers produces the positions instead.
SELECT c.CodeID, n.n AS pos,
       SUBSTRING(c.CodeValue, n.n, 1) AS ch
FROM dbo.Codes AS c
JOIN dbo.Numbers AS n
  ON n.n BETWEEN 1 AND LEN(c.CodeValue);

A numbers table is shared infrastructure: document its maximum value and extend it before longer inputs arrive. Do not assume sys.all_objects always has enough rows for the longest code. A simple permanent table and an index are more predictable for repeated production use.

Keep a Loop UDF for Repeated Characters as the Baseline

To judge a rewrite, compare against the actual existing scalar function, not an imaginary slow loop. A minimal baseline scans the code and skips repeated characters, appending each character only on its first appearance. It uses the same collation and empty-input rules as the set version. Capture the old function's code and result before replacing it.

CREATE OR ALTER FUNCTION dbo.RemoveRepeatedLoop
(@code varchar(100))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @out varchar(100) = '', @i int = 1,
            @ch varchar(1);
    WHILE @i <= LEN(@code)
    BEGIN
        SET @ch = SUBSTRING(@code, @i, 1);
        IF CHARINDEX(@ch, @out) = 0 SET @out += @ch;
        SET @i += 1;
    END;
    RETURN @out;
END;
GO

The function is a comparison target, not a recommendation to add a new UDF. It needs a separate batch because CREATE OR ALTER FUNCTION has batch rules. Check its behavior with NULL and trailing spaces against the set query before timing either one.

Time Both on 100,000 Rows

Build a 100,000-row test table with representative code lengths and duplicate patterns, then run both forms under SET STATISTICS TIME and IO with actual plans. Use the same input snapshot, compare checksums or exact results, and repeat warm-cache runs. A scalar UDF can be inlined under some versions and settings, so inspect the actual plan rather than assuming row-by-row execution from its definition alone.

I record CPU, elapsed time, reads, spills, and tempdb use. The set version can win by avoiding repeated function calls, but its character expansion can lose on very long values. A fair conclusion reports the data shape and compatibility level. The fastest query that changes case sensitivity or drops trailing spaces is not a valid improvement.

Benchmark a Representative Table

To exercise 100,000 rows, build a test table with a stable ID and code, mixing short values, long values, and duplicate-heavy values. Run the loop UDF and the set query against the same table under SET STATISTICS TIME ON. Materialize each result into a separate temporary output table so client-grid rendering does not dominate the test. Compare every output row by ID with EXCEPT in both directions before trusting a faster duration.

DROP TABLE IF EXISTS #CodeBench;
;WITH n AS
(
  SELECT TOP (100000)
         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS CodeID
  FROM sys.all_objects a CROSS JOIN sys.all_objects b
)
SELECT CodeID,
       CASE WHEN CodeID % 2 = 0 THEN 'AAB--12'
            ELSE 'BANANA-1001' END AS CodeValue
INTO #CodeBench FROM n;
SET STATISTICS TIME ON;
SELECT CodeID, dbo.RemoveRepeatedLoop(CodeValue) AS cleaned
INTO #LoopResult FROM #CodeBench;
SELECT c.CodeID, COALESCE(x.cleaned, '') AS cleaned
INTO #SetResult
FROM #CodeBench AS c
OUTER APPLY
(
  SELECT STRING_AGG(CAST(z.ch AS varchar(max)), '')
         WITHIN GROUP (ORDER BY z.pos) AS cleaned
  FROM
  (
    SELECT g.value AS pos,
           SUBSTRING(c.CodeValue,g.value,1) AS ch,
           ROW_NUMBER() OVER
             (PARTITION BY SUBSTRING(c.CodeValue,g.value,1)
              ORDER BY g.value) AS rn
    FROM GENERATE_SERIES(1,LEN(c.CodeValue)) AS g
  ) AS z
  WHERE z.rn = 1
) AS x;
SET STATISTICS TIME OFF;
SELECT CodeID, cleaned FROM #LoopResult
EXCEPT SELECT CodeID, cleaned FROM #SetResult;

The benchmark builds both result tables and compares them. Check the reverse EXCEPT too, then capture the actual plans. Two repeating values across 100,000 rows are useful for a smoke test, but a convincing benchmark varies length and character distribution like the real workload. Repeat the run after warming the cache, record CPU separately from elapsed time, and compare maximum memory grants and tempdb spills before selecting the production form.

Watch Collation and Character Boundaries

ROW_NUMBER partitions under SQL comparison rules. If the application wants byte-identical characters, use an explicit binary collation in both the set query and the loop baseline. If it wants linguistic equivalence, test accented letters and case. A varchar value can also contain multibyte characters under UTF-8 collations, where character positions and byte counts differ. Validate the exact data type and collation before calling the rewrite equivalent. For a code with trailing spaces, use a length rule that preserves them or explicitly trim first. Document that choice in the data contract, since LEN alone silently excludes those positions from the generated series.

Related reading on this blog: SQL SERVER 2022: GENERATE_SERIES Function and Remove Duplicate Chars From String: Part 2.

A character-removal loop is not the baseline, it is one method to compare with a set-based query.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.