SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tom Thomson / character ordering / Latest Posts

RE: character ordering

bochambers — Wed, 07 Nov 2012 19:20:57 GMT

I'm supprised no-one has mentioned another implication of collation ordering and special characters that can be quite important; the impact on LIKE clauses that use set's of characters e.g. [code="sql"]DECLARE @var CHAR(1) = '¾'SELECT @varWHERE @var LIKE '%[0-9]%'[/code]Similarly, this applies to the accented characters, so trying to use [a-z0-9] will not yield the standard set of alphanumeric characters that might be expected.Which given the low success rate on this question (including my own error), I would suspect that this is a common oversight.So thank you for what is actually an excellent question.

RE: character ordering

john.arnott — Mon, 05 Nov 2012 12:15:26 GMT

Thanks for the QOD, Tom. I appreciate the heads-up that a deceptively simple condition (like "where myLetter between 'a' and 'Z'") could result in a surprise if one expects it to filter just the 26 English letters in upper or lower case. The answer of exactly how many it would include is, of course not nearly so important as the possibility that it's greater than 52 (or 62 in your original poser which starts with the numeric digits).

RE: character ordering

L' Eomot Inversé — Mon, 05 Nov 2012 08:26:18 GMT

[quote][b]vk-kirov (11/2/2012)[/b][hr]Incorrect. Try the following script and you'll get 62 characters:[code="sql"]CREATE DATABASE qod_collation_db COLLATE Japanese_CI_AS_KS_WS;GOUSE qod_collation_db;GOWITH Numbers AS( SELECT 0 AS n UNION ALL SELECT n + 1 FROM Numbers WHERE n < 255)SELECT n AS code, CHAR(n) AS symbolFROM Numbers AS n WHERE CHAR(n) COLLATE Latin1_General_CI_AS BETWEEN '0' COLLATE Latin1_General_CI_AS AND 'Z' COLLATE Latin1_General_CI_ASOPTION(MAXRECURSION 255);GOUSE master;GODROP DATABASE qod_collation_db;GO[/code]What's interesting if we execute 'SELECT CHAR(n)' on a Japanese_CI_AS_KS_WS database we'll get NULLs for the values of 'n' from 129 to 159 and from 224 to 252.[/quote]Yes, of course, you are completely right. The CHAR function depends on the effective default collation, not on any other collation however specified. It's fairly easy to get round it, although somewhat verbose: instad of "CHAR(n) collate Latin1_General_CI_AS" we can use "cast(NCHAR(n) COLLATE Latin1_General_CI_AS as CHAR(1))", resulting in [code="sql"]CREATE DATABASE qod_collation_db COLLATE Japanese_CI_AS_KS_WS;GOUSE qod_collation_db;GOWITH Numbers AS( SELECT 0 AS n UNION ALL SELECT n + 1 FROM Numbers WHERE n < 255)SELECT n AS code, cast(NCHAR(n) COLLATE Latin1_General_CI_AS as CHAR(1)) AS symbolFROM Numbers WHERE cast(NCHAR(n) COLLATE Latin1_General_CI_AS as CHAR(1)) BETWEEN '0' COLLATE Latin1_General_CI_AS AND 'Z' COLLATE Latin1_General_CI_ASOPTION(MAXRECURSION 255);GOUSE master;GODROP DATABASE qod_collation_db;GO[/code]which delivers 132 rows (and displays the Latin1 symbols). I ought to have got that right first time, as I had plenty involvement over the years on internationalisation of text in SQL databases involving Latin, Cyrillic, Arabix, and both traditional and simplified Chinese writing. Perhaps I never noticed the behaviousr of the CHAR function because after I decreed that we would use Unicode for all text I didn't bother to look at CHAR - if so that was careless of me, if not then forgetting it was careless of me. :blush:[quote]Another example (sorry for using a non-Latin character here; I don't know how you will see that character – in its original form, as a rectangle or something else; actually I'm using a cyrillic letter [url]http://en.wikipedia.org/wiki/Tse_(Cyrillic)[/url]):[/quote]original form for me, although of course I normally use nchar(1094) rather than char(246) for that character. My default display font is Lucida Sans Unicode so I tend to see most characters (Arial Unicode MS has slightly better coverage, but it makes an ugly mess of quite a lot of symbols - perhaps has bad spacing hints).Interesting that the upper-case tse shows as '?' both in Latin and in Japanese, while the lower case tse shows as white space in Japanese.

RE: character ordering

lukas-311170 — Sun, 04 Nov 2012 08:00:01 GMT

Interesting remarks form vv-kirov, though l'eomot's querie is not all incorrect.The point is that the function char() will give an output based on the character set of the code page in the collation of the database (at least that is the behaviour vv-kirov's example shows).So l'eomot's querie will only give the right answer if the collation has already a latin1 codepage base, because when running it on a cyrillic or japanese based collation its output can give totally other characters. A "collate" function afterwards (in the "between" statement) cannot change the character itself, only its position in the order.

RE: character ordering

Raghavendra Mudugal — Fri, 02 Nov 2012 07:59:34 GMT

:cool: (actually I used extreme advanced skill to make the choice, and I still got it wrong.... but got to learn on load of things)Awesome question. :-) Thank you Tom

RE: character ordering

Koen Verbeeck — Fri, 02 Nov 2012 04:36:25 GMT

Great question Tom, thanks!

RE: character ordering

vk-kirov — Fri, 02 Nov 2012 03:05:03 GMT

[quote][b]L' Eomot Inversé (11/1/2012)[/b][hr]Actually, I suspect that the correct way to write the condition, because a collate clause applies only to a single column or constant or variable, is[code] where CHAR(I) COLLATE Latin1_General_CI_AS BETWEEN '0' COLLATE Latin1_General_CI_AS AND 'Z' COLLATE Latin1_General_CI_AS and I < 256[/code]That would (I hope) ensure that default collations are not used anywhere, so whatever the server default and the database default are the query will always deliver the same answer. [/quote]Incorrect. Try the following script and you'll get 62 characters:[code="sql"]CREATE DATABASE qod_collation_db COLLATE Japanese_CI_AS_KS_WS;GOUSE qod_collation_db;GOWITH Numbers AS( SELECT 0 AS n UNION ALL SELECT n + 1 FROM Numbers WHERE n < 255)SELECT n AS code, CHAR(n) AS symbolFROM Numbers AS n WHERE CHAR(n) COLLATE Latin1_General_CI_AS BETWEEN '0' COLLATE Latin1_General_CI_AS AND 'Z' COLLATE Latin1_General_CI_ASOPTION(MAXRECURSION 255);GOUSE master;GODROP DATABASE qod_collation_db;GO[/code]What's interesting if we execute 'SELECT CHAR(n)' on a Japanese_CI_AS_KS_WS database we'll get NULLs for the values of 'n' from 129 to 159 and from 224 to 252.Another example (sorry for using a non-Latin character here; I don't know how you will see that character – in its original form, as a rectangle or something else; actually I'm using a cyrillic letter [url]http://en.wikipedia.org/wiki/Tse_(Cyrillic)[/url]):[code="sql"]CREATE DATABASE qod_collation_db COLLATE Cyrillic_General_CI_AS;GOUSE qod_collation_db;GOSELECT CHAR(246) AS Cyrillic, CHAR(246) COLLATE Latin1_General_CI_AS AS Latin, CHAR(246) COLLATE Japanese_CI_AS_KS_WS AS Japanese;GOUSE master;GODROP DATABASE qod_collation_db;GO[/code]Here is the result set:[code="plain"]Cyrillic Latin Japanese-------- ----- --------ц ? [/code](Note that the 'Japanese' value is a whitespace.)

RE: character ordering

lukas-311170 — Thu, 01 Nov 2012 18:01:33 GMT

Right! it's collate latin1_general_ci_as - Case INsensitive... (sorry for posting previous post too soon! )

RE: character ordering

lukas-311170 — Thu, 01 Nov 2012 17:55:21 GMT

Strange, when i run the query that's in my previous post (with the "order by char(i) collate latin1_general_ci_as") i have the order .... g G H h ... Sometimes the capital is listed before and sometimes after ....What am I missing here ?? :w00t:

RE: character ordering

lukas-311170 — Thu, 01 Nov 2012 17:42:47 GMT

I don't think the collation specification is needed after the single character constants ('0' or 'Z') in the "between"-expression, but it is after the expression 'CHAR(i)' - at least, this query gives me the right number of characters (139), although my server collation is different than latin1_general_ci_as. (it is sql_latin1_general_cp1_ci_as).SELECT i,CHAR(i) FROM [Test].[dbo].[Tally] where CHAR(i) collate latin1_general_ci_as between '0' and 'Z' and i<256 order by CHAR(i) collate latin1_general_ci_asI added the collation specification in the order by clause: this shows the correct order : 0, 1/4, ...etc.btw: my first answer was wrong, nice question!

RE: character ordering

KWymore — Thu, 01 Nov 2012 12:54:28 GMT

[quote][b]paul.knibbs (11/1/2012)[/b][hr][quote][b]mtassin (11/1/2012)[/b][hr]Oh and of course I got it wrong... because I just went with[code]SELECT ASCII('Z')-ASCII('0')[/code][/quote]That's pretty much what I did, but I did it in my head rather than using code. I was 100% sure this would be the wrong answer, mind you, I was just curious to see what the right one was! :-)[/quote]ditto!

RE: character ordering

L' Eomot Inversé — Thu, 01 Nov 2012 12:09:55 GMT

[quote][b]mtassin (11/1/2012)[/b][hr]What's interesting for me is that the query from the original question returns 120 rows.[code]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) [/code]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.[/quote]Actually, I suspect that the correct way to write the condition, because a collate clause applies only to a single column or constant or variable, is[code] where CHAR(I) COLLATE Latin1_General_CI_AS BETWEEN '0' COLLATE Latin1_General_CI_AS AND 'Z' COLLATE Latin1_General_CI_AS and I < 256[/code]That would (I hope) ensure that default collations are not used anywhere, so whatever the server default and the database default are the query will always deliver the same answer. It was careless of me to use a query that doesn't correctly eliminate default collations in my explanation.

RE: character ordering

Meow Now — Thu, 01 Nov 2012 10:13:00 GMT

I was dumbfounded after the correct answer was not listed: 11. The question clearly states "How many 8-bit characters are there between '0' and 'Z' (inclusive) in the collation Latin1_General_CI_AS?" There are exactly 11 8-bit characters in the string ['0' and 'Z'] :-PI thought this crowd liked to be literal to the point of absurdity?

RE: character ordering

paul.knibbs — Thu, 01 Nov 2012 08:41:27 GMT

[quote][b]mtassin (11/1/2012)[/b][hr]Oh and of course I got it wrong... because I just went with[code]SELECT ASCII('Z')-ASCII('0')[/code][/quote]That's pretty much what I did, but I did it in my head rather than using code. I was 100% sure this would be the wrong answer, mind you, I was just curious to see what the right one was! :-)

RE: character ordering

mtassin — Thu, 01 Nov 2012 08:09:40 GMT

Oh and of course I got it wrong... because I just went with[code]SELECT ASCII('Z')-ASCII('0')[/code]Sadly I'm just too dang American... A thru Z, 0 thru 9... sure... but ASCII isn't Latin1_General_CI_AS

RE: character ordering

mtassin — Thu, 01 Nov 2012 08:06:42 GMT

[quote][b]SQL Kiwi (11/1/2012)[/b][hr][quote][b]derek.colley (11/1/2012)[/b][hr]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.[/quote]The specific answer isn't useful, but the [b]process of writing a query to answer it[/b] surely could be. I had no idea of the correct answer so I wrote a query very similar to that given in the answer:[code="sql"]SELECT * FROM dbo.Numbers AS n WHERE CHAR(n) COLLATE Latin1_General_CI_AS BETWEEN '0' AND 'Z';[/code]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.[/quote]What's interesting for me is that the query from the original question returns 120 rows.[code]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) [/code]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.

RE: character ordering

vk-kirov — Thu, 01 Nov 2012 06:51:00 GMT

[quote][b]Nils Gustav Stråbø (11/1/2012)[/b][hr][quote][b]vk-kirov (11/1/2012)[/b][hr]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.[/quote]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.[/quote]Not so simple :-)Try the following code (based on Paul's query):[code="sql"]CREATE DATABASE qod_collation_db COLLATE Japanese_CI_AS_KS_WS;GOUSE qod_collation_db;GOWITH Numbers AS( SELECT 0 AS n UNION ALL SELECT n + 1 FROM Numbers WHERE n <= 255)SELECT n AS code, CHAR(n) AS symbolFROM Numbers AS n WHERE CHAR(n) COLLATE Latin1_General_CI_AS BETWEEN '0' AND 'Z'OPTION(MAXRECURSION 256);GOUSE master;GODROP DATABASE qod_collation_db;GO[/code]It returns 62 characters. With the Cyrillic_General_CS_AS collation, you'll get 63 characters; with Vietnamese_CI_AS – 131 etc.

RE: character ordering

G Bryant McClellan — Thu, 01 Nov 2012 06:17:05 GMT

I'd agree that the intent was more important than the answer. Well done.

RE: character ordering

honza.mf — Thu, 01 Nov 2012 06:05:29 GMT

[quote][b]Ross.M (11/1/2012)[/b][hr][quote][b]honza.mf (11/1/2012)[/b][hr][quote][b]Ross.M (11/1/2012)[/b][hr]... 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.[/quote]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.[/quote]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.[/quote]See Tom's answer above. Yes the number is pointless. The methods how to obtain it are important.

RE: character ordering

(Bob Brown) — Thu, 01 Nov 2012 05:56:30 GMT

I guess some people feel this was a good question?

RE: character ordering

Nils Gustav Stråbø — Thu, 01 Nov 2012 05:31:42 GMT

Excellent question that requires knowledge of how collations work. :-)More of these Tom.

RE: character ordering

Ross.M — Thu, 01 Nov 2012 05:29:20 GMT

[quote][b]honza.mf (11/1/2012)[/b][hr][quote][b]Ross.M (11/1/2012)[/b][hr]... 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.[/quote]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.[/quote]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.

RE: character ordering

Nils Gustav Stråbø — Thu, 01 Nov 2012 05:27:02 GMT

[quote][b]vk-kirov (11/1/2012)[/b][hr]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.[/quote]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.

RE: character ordering

L' Eomot Inversé — Thu, 01 Nov 2012 05:24:54 GMT

[quote][b]SQL Kiwi (11/1/2012)[/b][hr]The specific answer isn't useful, but the [b]process of writing a query to answer it[/b] surely could be. I had no idea of the correct answer so I wrote a query very similar to that given in the answer:[code="sql"]SELECT * FROM dbo.Numbers AS n WHERE CHAR(n) COLLATE Latin1_General_CI_AS BETWEEN '0' AND 'Z';[/code]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.[/quote]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.

RE: character ordering

honza.mf — Thu, 01 Nov 2012 05:14:56 GMT

[quote][b]Ross.M (11/1/2012)[/b][hr]... 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.[/quote]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.

RE: character ordering

Paul White — Thu, 01 Nov 2012 04:29:58 GMT

[quote][b]derek.colley (11/1/2012)[/b][hr]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.[/quote]The specific answer isn't useful, but the [b]process of writing a query to answer it[/b] surely could be. I had no idea of the correct answer so I wrote a query very similar to that given in the answer:[code="sql"]SELECT * FROM dbo.Numbers AS n WHERE CHAR(n) COLLATE Latin1_General_CI_AS BETWEEN '0' AND 'Z';[/code]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.

RE: character ordering

Ross.M — Thu, 01 Nov 2012 04:24:08 GMT

[quote][b]honza.mf (11/1/2012)[/b][hr][quote][b]derek.colley (11/1/2012)[/b][hr]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.[/quote]For example it's good to know there are accented letters in the area. There are both lowercase and uppercase letters.Without tnis knowledge you are not able to understand string comparisons.[/quote]I'm with Derek on this one, it's a good question but very irrelevant, and I was very surprised to see that I was in the largest percentage who also all got it wrong. I don't understand where this would come in useful, we all know this collation has accented characters which it is sensitive to, and uppercase and lowercase characters which it is insensitive to, the collation name tells us this; 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.

RE: character ordering

derek.colley — Thu, 01 Nov 2012 03:51:02 GMT

[quote]For example it's good to know there are accented letters in the area. There are both lowercase and uppercase letters.Without tnis knowledge you are not able to understand string comparisons.[/quote]I'm already aware of the existence of accented characters in this collation, without accented characters the collation would not need to be marked as AI or AS.When doing string comparisons, I would likewise be aware of accents and case differences on letters and non-standard A-Z alphabetic characters anyway.I would also consult a character map for the collation if comparing by ASCII / Unicode decimal or hex values (much easier than messing around with set-based or RBAR CHAR()-based SQL code).Your point doesn't detract from mine, which was that this is a pointless question, reliant on the reader's ability to count.

RE: character ordering

honza.mf — Thu, 01 Nov 2012 03:35:59 GMT

RE: character ordering

vk-kirov — Thu, 01 Nov 2012 03:12:01 GMT

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.

RE: character ordering

derek.colley — Thu, 01 Nov 2012 02:47:02 GMT

As with most QotDs, I try and answer them, right or wrong, before researching the answer, so I can identify gaps in my knowledge rather than score points.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.It's a numbers question. It's a bit like asking, 'how many system tables exist in the MSDB after a vanilla installation of SQL Server 2005 with SP2 (on a full moon in the Northern Hemisphere while wearing Wellington boots and a silly hat, or any other irrelevance you care to name)?' - the answer can be found, but why would it be relevant to anyone in a DBA/BI/dev position?I think my views are echoed in the results so far, one of the few QotDs where wrong answers (probably mostly guesses) outnumber right ones.Sorry to be so harsh. I, too, am without my morning coffee.

RE: character ordering

honza.mf — Thu, 01 Nov 2012 02:36:43 GMT

My first estimate was something near 75 (10 digits, 26 uppercase, 26 lowercase) but then I remembered the accents and found a table.

RE: character ordering

Stewart "Arturius" Campbell — Thu, 01 Nov 2012 02:10:36 GMT

Och, tripped myself up on the CI option of the collation - lesson - do [b]NOT[/b] attempt to do the QotD before having your first cup of coffee in the morning.Good question, Tom.

character ordering

L' Eomot Inversé — Wed, 31 Oct 2012 22:10:14 GMT

Comments posted to this topic are about the item [B]character ordering[/B]