SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


character ordering


character ordering

Author
Message
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25843 Visits: 12494
SQL Kiwi (11/1/2012)
The specific answer isn't useful, but the process of writing a query to answer it surely could be. I had no idea of the correct answer so I wrote a query very similar to that given in the answer:


SELECT *
FROM dbo.Numbers AS n
WHERE
CHAR(n) COLLATE Latin1_General_CI_AS
BETWEEN '0' AND 'Z';


The idea of QotDs that require the reader to write T-SQL code to find the answer intrigues me. If that was Tom's intention (as I suspect it was) and/or to highlight the usefulness of a Numbers table, I applaud him.

You've spotted my secondary intentions. I thought an interesting change from providing code that people could cut and paste into a query window and run would be to make them write their own code to get the answer, and that some people might learn something from being pointed at Tally tables and Jeff's excellent article again.

But I also remembered developers who were very annoyed to discover that 0 to Z contained not only what they though of as "real" consonants (including the obvious German, French, and Spanish ones), "real" (single digit integer) numerics, and "real" vowels (the usual 5 plus versions with actute, grave, circumflex, and umlaut diacritics) but a host of other things; although I'm more of a developer than a DBA myself, their reactions (for example griping about the collation being senseless, claiming that there are no useful collations or that SQL is broken if it thinks '¾' is a numeric character or that there "should" only be 72 alphanumeric characters) managed to put me firmly into the foul-tempered DBA camp. So my primary intention was to have people discover that there are a lot more than 72 alphanumeric characters in the ascii character set with the default collation.

I think the count of answers so far makes it pretty clear that most people haven't a clue what characters fall in there - two thirds of responses have chosen one of the three lowest options: 36 (26 letters of the English alphabet, forgetting that there are two cases, plus 10 numeric digits) ,43 (36 plus 7: áâéèêîô), or 62 (English alphabet with two cases, ten numeric digits). So maybe two thirds of people who have seen the question have learnt something useful - not that the answer is 139 (who cares about the exact number, as long as they can find it if they ever need it), but that the answer is quite a lot bigger than 72.

Tom

Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3629 Visits: 3575
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.
RossRoss
RossRoss
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 763
honza.mf (11/1/2012)
Ross.M (11/1/2012)
... however for the sake of understanding string comparisons, we have no use for knowing how many characters are between 0 and Z, we just need to know the collation type and have an ascii table handy.

This is one of the tricks of this question. Collation and ascii tables are two different things.
Collation ordering does not copy ascii ordering of characters, it's little bit more sofisticated.


I understand that, my point though, is that the order and total are irrelevant, we never need to know how many characters are between 0 and Z on a particular collation.
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3629 Visits: 3575
Excellent question that requires knowledge of how collations work. :-)
More of these Tom.
(Bob Brown)
(Bob Brown)
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1059 Visits: 1145
I guess some people feel this was a good question?
honza.mf
honza.mf
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2545 Visits: 1323
Ross.M (11/1/2012)
honza.mf (11/1/2012)
Ross.M (11/1/2012)
... however for the sake of understanding string comparisons, we have no use for knowing how many characters are between 0 and Z, we just need to know the collation type and have an ascii table handy.

This is one of the tricks of this question. Collation and ascii tables are two different things.
Collation ordering does not copy ascii ordering of characters, it's little bit more sofisticated.


I understand that, my point though, is that the order and total are irrelevant, we never need to know how many characters are between 0 and Z on a particular collation.

See Tom's answer above. Yes the number is pointless. The methods how to obtain it are important.



See, understand, learn, try, use efficient
© Dr.Plch
Bryant McClellan
Bryant McClellan
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1443 Visits: 546
I'd agree that the intent was more important than the answer. Well done.

------------
Buy the ticket, take the ride. -- Hunter S. Thompson
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4336 Visits: 4408
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.
mtassin
mtassin
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7220 Visits: 72521
SQL Kiwi (11/1/2012)
derek.colley (11/1/2012)
I'm afraid this question was just too obscure for me. It's a good question, sure, but why would I need to know this or reference it in any way? I mean, we have hundreds of databases, most of which actually use this collation, and this nugget has never, and will never come in useful.

The specific answer isn't useful, but the process of writing a query to answer it surely could be. I had no idea of the correct answer so I wrote a query very similar to that given in the answer:


SELECT *
FROM dbo.Numbers AS n
WHERE
CHAR(n) COLLATE Latin1_General_CI_AS
BETWEEN '0' AND 'Z';


The idea of QotDs that require the reader to write T-SQL code to find the answer intrigues me. If that was Tom's intention (as I suspect it was) and/or to highlight the usefulness of a Numbers table, I applaud him.


What's interesting for me is that the query from the original question returns 120 rows.

select CHAR(I),I from Tally
where char(I) between '0' and 'Z' collate latin1_general_ci_as
and I < 256 order by CHAR(I)



However, your query gives 139. I also realize why here (one's apply the collation to 0 thru Z the other to the result of the CHAR function. But I did find that interesting.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
mtassin
mtassin
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7220 Visits: 72521
Oh and of course I got it wrong... because I just went with

SELECT ASCII('Z')-ASCII('0')



Sadly I'm just too dang American... A thru Z, 0 thru 9... sure... but ASCII isn't Latin1_General_CI_AS



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search