-1

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;

enter image description here

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
Ludovic Aubert
  • 9,534
  • 4
  • 16
  • 28
  • When you have a `varchar(1)` how are you expecting the numbers 10 and over to fit in it? 10 has 2 characters, `'1'` and `'0'`. Or do you actually want `CHAR(n)`, not a `varchar` with the value of `n` (for example `'10'`)? – Thom A Oct 27 '21 at 13:47
  • my ultimate goal is to detect control characters in a column containing file system absolute file paths – Ludovic Aubert Oct 27 '21 at 13:51
  • Can the first argument to CHARINDEX be an int ? I dont want to detect '3' in an absolute file system path, I want to detect the special character with ascii code 3. – Ludovic Aubert Oct 27 '21 at 13:51
  • 1
    *"Can the first argument to CHARINDEX be an int"* Yes, but it would be implicitly converted to a character expression. So `100` would be implicitly converted to `'100'`. – Thom A Oct 27 '21 at 13:54
  • That's what I don't want – Ludovic Aubert Oct 27 '21 at 13:55
  • So what *do* you want... – Thom A Oct 27 '21 at 13:56

1 Answers1

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;
Jorge Bugal
  • 429
  • 2
  • 3