Nils Gustav Stråbø (11/1/2012)
vk-kirov (11/1/2012)
Note that the QOD query will return 139 characters if it runs on a database with the Latin1_General_CI_AS (or similar) collation. When running on a database with another collation, the results may vary. For example, for a Vietnamese_CI_AS database the query returns 131 characters, for a Cyrillic_General_CS_AS database – 158 characters, for a Japanese_CI_AS_KS_WS database – 122 characters, for a SQL_EBCDIC273_CP1_CS_AS (?!) database – 15 characters. But the answer given is correct though.That is because the query in the answer is missing a COLLATE for the '0' in BETWEEN, or you can simply use SQL Kiwi's example.
Not so simple 🙂
Try the following code (based on Paul's query):
CREATE DATABASE qod_collation_db COLLATE Japanese_CI_AS_KS_WS;
GO
USE qod_collation_db;
GO
WITH Numbers AS
( SELECT 0 AS n
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n <= 255
)
SELECT n AS code, CHAR(n) AS symbol
FROM Numbers AS n
WHERE CHAR(n) COLLATE Latin1_General_CI_AS BETWEEN '0' AND 'Z'
OPTION(MAXRECURSION 256);
GO
USE master;
GO
DROP DATABASE qod_collation_db;
GO
It returns 62 characters. With the Cyrillic_General_CS_AS collation, you'll get 63 characters; with Vietnamese_CI_AS – 131 etc.