Click here to Skip to main content
15,903,012 members
Please Sign up or sign in to vote.
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
Comments
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

1 solution

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
 
Share this answer
 
Comments
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!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900