Create column listing ascii control keys

Question

I need to create a table containing ascii control keys (code range between 0 and 31).

My first try:

CREATE TABLE control_characters(
    n int,
    [character] varchar(1)
);

WITH cte AS (
    SELECT 0 AS n, CAST(0 AS varchar(1)) AS c

    UNION ALL

    SELECT n+1 AS n, CAST(n+1 AS varchar(1)) AS c
    FROM cte
    WHERE n<=30
)
INSERT INTO control_characters(n, [character])
SELECT n,c FROM cte;

SELECT * FROM control_characters;

SELECT CAST(cc.[character] AS int) AS pb, s.*
FROM file_extract_staging s
CROSS JOIN control_characters cc
WHERE CHARINDEX(cc.[character], s.absolute_path_unique) != 0

Answer 1

You should use the CHAR function for it.

Also, it's more efficient to use char(1) instead of varchar(1).

CREATE TABLE control_characters(
    n int,
    [character] char(1)
);
GO
WITH cte AS (
    SELECT 0 AS n, CHAR(0) AS c

    UNION ALL

    SELECT n+1 AS n, CHAR(n+1) AS c
    FROM cte
    WHERE n<=30
)
INSERT INTO control_characters(n, [character])
SELECT n,c FROM cte;
GO
SELECT * FROM control_characters;