15,903,012 members
5.00/5 (1 vote)
See more:
Having a dense/senior moment today...

I have an (inherited) SQL query running against a cte that now produces rows numbered from 1 to 16384. I'm expecting it to generate column letters in the range "A" to "XFC" (Previously generated "A" to "ZZ")

This should work
SQL
```SELECT l, CASE WHEN l >= 703 THEN Char(65 + (l - 703) / 702 % 702) ELSE '' END
+ CASE WHEN l >=27 then Char(65 + (l - 27) / 26 % 26) ELSE '' END
+ Char(65 + (l  - 1) % 26)
FROM eFinal
WHERE l < 16384
ORDER BY l;```
In fact, it does work - UNTIL we get to row 6787 - instead of returning "JAA" it reverts to "IAA" (through to "IAZ" and from there does some weird and wonderful things (including non-ascii characters))

I can get around it with
SQL
```SELECT l, (CASE WHEN l >= 6787 THEN Char((65 + (l - 703) / 702 % 702) + 1)
WHEN l >= 703 THEN  Char(65 + (l - 703) / 702 % 702) ELSE '' END)
+ (CASE WHEN l > 27 THEN (Char(65 + (l - 27) / 26 % 26)) ELSE '' END)
+(Char(65 + (l  - 1) % 26))
FROM eFinal
WHERE l < 16384
ORDER BY l;```
Which works all the way to "XFC".

What I don't understand is... why?

I'm struggling to understand the significance of 6786 as a key number - although I did get overexited when I realised it's 26 x 255 but `l` is a bigint so not relevant?

What I have tried:

Full code to reproduce the behaviour
SQL
```;with
cte1 AS (select n from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) AS X(n))
,cte2 AS (SELECT a.n FROM cte1 a CROSS JOIN cte1 b)	-- 10*10
,cte3 AS (SELECT a.n FROM cte1 a CROSS JOIN cte2 b)	-- 10*100
,cte4 AS (SELECT a.n FROM cte2 a CROSS JOIN cte3 b )	-- 1000*100
,eFinal AS (SELECT l = ROW_NUMBER() OVER (ORDER BY a.n) FROM cte2 a CROSS JOIN cte3 b )
SELECT l, CASE WHEN l >= 703 THEN Char(65 + (l - 703) / 702 % 702) ELSE '' END
+ CASE WHEN l >=27 then Char(65 + (l - 27) / 26 % 26) ELSE '' END
+ Char(65 + (l  - 1) % 26)
FROM eFinal
--WHERE l < 16384
where l BETWEEN 6780 AND 6790
ORDER BY l;```
Posted
Maciej Los 13-Mar-24 14:16pm
I love this part: "Having a dense/senior moment today...". I understand it as "pomroczność jasna" (in polish language). I doubt there's a direct translation (from polish to english), but i hope this will make you a day :)
CHill60 14-Mar-24 5:18am
:-D

## Solution 1

Because your math is wrong! :D
SQL
```DECLARE @cnt INT = 6785;
WHILE @cnt < 6789
BEGIN
SELECT @cnt
, (@cnt - 27) / (26 * 26)
, CASE WHEN (@cnt - 27 / (26 * 26)) > 0 THEN CHAR(65 + ((@cnt - 27) / (26 * 26))) ELSE '' END
, Char(65 + (@cnt - 27) / 26 % 26)
, Char(65 + (@cnt  - 1) % 26)
SET @cnt = @cnt + 1;
END```

CHill60 12-Mar-24 12:13pm
That's giving me KAA instead of JAA when I plug it in to my query but that's probably another error on my part - I'll revisit that in a moment. Why on earth I thought 26 * 26 was 702!
I'll get my coat. (It's been a long day)
Maciej Los 13-Mar-24 14:12pm
5ed!