InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 15:19:08 GMT20http://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxGreat question, thanks!I lost a point here, but it was well worth it as I hadn't even considered collation when I answered.Wed, 30 Mar 2011 21:42:40 GMTscottm30http://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]Tom.Thomson (9/22/2010)[/b][hr]A beautiful question, which may have made some of the backwoodsmen notice that there are collations other than the one which fits US ASCII perfectly and fits nothing else. But I'm not holding my breath while waiting for the average US (or should that be "U/S"?) DBA or DB designer to notice our weird "foreign" collation stuff.[/quote]Thank you.Thu, 23 Sep 2010 00:12:22 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxA beautiful question, which may have made some of the backwoodsmen notice that there are collations other than the one which fits US ASCII perfectly and fits nothing else. But I'm not holding my breath while waiting for the average US (or should that be "U/S"?) DBA or DB designer to notice our weird "foreign" collation stuff.Wed, 22 Sep 2010 18:57:07 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxI almost picked "It Depends" because, but I thought no, it couldn't be possible with a standard VARCHAR. Learned another thing today, at the cost of a point. :-)Tue, 01 Jun 2010 20:37:17 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]gserdijn (5/27/2010)[/b][hr]First thought: How can values like 'AA' be found with LIKE '_' ? Zero rows. Click. Zero points.Next time I will wait for the caffeine to do its work. I learned something, hope I remember it when I need it. ;-) [/quote]I was surprised too. I watched it after one of my coworkers came with a question: "How it is possible, this query returned this data?" His pattern was little bit more complicated, something like '__[_]_[_][[]%'.And the best point: I could write a question with "It depends" option:-)Thu, 27 May 2010 01:01:18 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxFirst thought: How can values like 'AA' be found with LIKE '_' ? Zero rows. Click. Zero points.Next time I will wait for the caffeine to do its work. I learned something, hope I remember it when I need it. ;-) Thu, 27 May 2010 00:41:53 GMTgserdijnhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]Paul White NZ (5/26/2010)[/b][hr]Very nice question today. I only found it easy because I have been doing stuff with unusual collations recently.There's a great set of visual tables showing sort (and comparison) orders at:[url]http://www.collation-charts.org/mssql/[/url]edit: used a code tag instead of url first time around :rolleyes:[/quote]Thank you for URL. It's very good source. It explained me a lot about some strange things I met before.Wed, 26 May 2010 12:27:14 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]Tom Garth (5/26/2010)[/b][hr]Quite correct. The actual number of rows in #t with a default installation is 20449. To get the 26 by 26 result requires[code="sql"]while ASCII(@a1) <= ASCII('Z') begin[/code], etc.This QotD turned out to be much more interesting than I initially thought it would.Thanks for the lesson![/quote]Yes, it was very interesting for me in the moment I dicovered the problem with 'CH'. Other interesting thoughts came to me when I wrote the question and the discussion is very useful.Wed, 26 May 2010 12:25:21 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]Tom Garth (5/26/2010)[/b][hr]The actual number of rows in #t with a default installation is 20449.[/quote]It depends :-D I've got 3969 rows with [i]my[/i] default installation.Wed, 26 May 2010 12:20:50 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxVery nice question today. I only found it easy because I have been doing stuff with unusual collations recently.There's a great set of visual tables showing sort (and comparison) orders at:[url]http://www.collation-charts.org/mssql/[/url]edit: used a code tag instead of url first time around :rolleyes:Wed, 26 May 2010 12:10:26 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]honza.mf (5/25/2010)[/b][hr][quote][b]jlennartz (5/25/2010)[/b][hr]If all characters were inserted into DB as upper case how come query is returning lower case?[/quote]It's another aspect of this question not mentioned yet: [b]All characters inserted into are not guaranted to be uppercase![/b]You feel they are uppercase due to "while @a2 <= 'Z'" condition. You must evaluate this condition in appropriate collation too. Most of us use case-insensitive collations and in such collations lowercase letters also fit this condition. And it si true for the special characters between 'Z' and 'a' in ASCII table like '[', '\', ']' - it's not obvious but for me it's clear. There can be even more characters that fit, it's collation dependant.If someone uses case sensitive collation, he will have all characters in uppercase.[/quote]Quite correct. The actual number of rows in #t with a default installation is 20449. To get the 26 by 26 result requires[code="sql"]while ASCII(@a1) <= ASCII('Z') begin[/code], etc.This QotD turned out to be much more interesting than I initially thought it would.Thanks for the lesson!Wed, 26 May 2010 10:46:01 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]Michael Poppers (5/25/2010)[/b][hr][quote][b]honza.mf (5/25/2010)[/b][hr]If someone uses case sensitive collation, he will have all characters in uppercase.[/quote]Sounds reasonable, but use COLLATE Czech_CS_AS (instead of the case-insensitive collation) -- SELECT DISTINCT will return three rows (instead of one), and one of the result rows appears (to my non-Czech eyes) to not be what in the Roman alphabet would be called "uppercase"![/quote]Sorry, it was my mistake as I haven't checked it. Case sensitivity of a collation is used probably in equality comparisons only. Loops in example are controled by less or equal operator, and it says 'a' <= 'Z'Wed, 26 May 2010 00:03:18 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]honza.mf (5/25/2010)[/b][hr]...I don't know our linguists (ÚPJČ) but this will be very hard to do. Orthographic rules are very rigid and cannot be changed so easy. I remember last great changes in czech orthography or several years ago changes Germans made. It was always painful.[/quote]First of all: very nice question!! I just "lost" one point, but learned something new. I was aware of the collation issue but I thought 'A' to 'Z' didn't make room for it.Regarding our orthography changes (I'm German): at least it allows me to write the way I'd like to: most probably there was/is/will_be a time and place where the spelling I use was/is/will_be valid :-)But it definitely makes it really hard to help your kids through (school) homework... Ooops, getting off topic. Sorry. Again: Nice QOTD!Tue, 25 May 2010 14:59:17 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]honza.mf (5/25/2010)[/b][hr][quote][b]jlennartz (5/25/2010)[/b][hr]If all characters were inserted into DB as upper case how come query is returning lower case?[/quote]It's another aspect of this question not mentioned yet: [b]All characters inserted into are not guaranted to be uppercase![/b]You feel they are uppercase due to "while @a2 <= 'Z'" condition. You must evaluate this condition in appropriate collation too. Most of us use case-insensitive collations and in such collations lowercase letters also fit this condition. And it si true for the special characters between 'Z' and 'a' in ASCII table like '[', '\', ']' - it's not obvious but for me it's clear. There can be even more characters that fit, it's collation dependant.If someone uses case sensitive collation, he will have all characters in uppercase.[/quote]Thank you for the answer it make sense, now. You won't get A-Z but whatever the Check equivqlent of their ascii number is.JerryTue, 25 May 2010 14:47:37 GMTjlennartzhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]honza.mf (5/25/2010)[/b][hr]If someone uses case sensitive collation, he will have all characters in uppercase.[/quote]Sounds reasonable, but use COLLATE Czech_CS_AS (instead of the case-insensitive collation) -- SELECT DISTINCT will return three rows (instead of one), and one of the result rows appears (to my non-Czech eyes) to not be what in the Roman alphabet would be called "uppercase"!Tue, 25 May 2010 14:43:44 GMTMichael Poppershttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]honza.mf (5/25/2010)[/b][hr]If someone uses case sensitive collation, he will have all characters in uppercase.[/quote]Sorry, ignore this reply and see my next reply :-). Thanks.Tue, 25 May 2010 14:40:20 GMTMichael Poppershttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]jlennartz (5/25/2010)[/b][hr]If all characters were inserted into DB as upper case how come query is returning lower case?[/quote]It's another aspect of this question not mentioned yet: [b]All characters inserted into are not guaranted to be uppercase![/b]You feel they are uppercase due to "while @a2 <= 'Z'" condition. You must evaluate this condition in appropriate collation too. Most of us use case-insensitive collations and in such collations lowercase letters also fit this condition. And it si true for the special characters between 'Z' and 'a' in ASCII table like '[', '\', ']' - it's not obvious but for me it's clear. There can be even more characters that fit, it's collation dependant.If someone uses case sensitive collation, he will have all characters in uppercase.Tue, 25 May 2010 14:23:45 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]honza.mf (5/25/2010)[/b][hr][quote][b]Michael Poppers (5/25/2010)[/b][hr][quote][b]malleswarareddy_m (5/25/2010)[/b][hr]...select string from #t where string COLLATE CZECH_CI_AS like '_'the above you have given returns givesCHChch¢h four rows....[/quote]I was wondering why my SQL Query Analyzer output apparently listed "ch" twice -- thanks for listing the accurate output (in which the fourth row is not "ch"). Now try SELECT DISTINCTing the column ;-) -- the #rows should tell us precisely how many LIKE '_' characters actually exist between 'A' and 'Z' :-).[/quote]Your idea with DISTINCT is great. It makes the things little bit more funny with case-insensitive collations.[/quote]If all characters were inserted into DB as upper case how come query is returning lower case?Tue, 25 May 2010 13:07:42 GMTjlennartzhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxI think of instead of 'It Depends' the last choice should have been collation dependent. By itself 'It depends' is not much of an answer.-- Mark D Powell --Tue, 25 May 2010 11:05:54 GMTMark D Powellhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxGreat questionTue, 25 May 2010 10:36:13 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxThanks for the question. I bombed this one, I should of looked at the category or went with my gut, everything always "depends".Tue, 25 May 2010 09:15:28 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]Vladan (5/25/2010)[/b][hr]Nice one, and you got me with that question. I absolutely forgot about "ch" and went with the popular "0" answer :crazy:So-called "letter" ch can create some confusion because of the special alphabetical order it has. Anyone expects words beginning with "ch..." under the letter C in a dictionary/encyclopedia - but if it is a Czech one, you'll find them under a separate heading "CH", not even close to "C" (it is G, H, Ch, I...). IMHO this should be addressed in some future release of Czech orthography and we should declare, that "CH" is in fact two letters and standard ordering rules would apply from that time (in fact, it is long overdue... but I'm afraid no one will bother with it and we will be stuck with this "letter" forever).[/quote]I don't know our linguists (ÚPJČ) but this will be very hard to do. Orthographic rules are very rigid and cannot be changed so easy. I remember last great changes in czech orthography or several years ago changes Germans made. It was always painful.Tue, 25 May 2010 09:03:03 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxNice one, and you got me with that question. I absolutely forgot about "ch" and went with the popular "0" answer :crazy:So-called "letter" ch can create some confusion because of the special alphabetical order it has. Anyone expects words beginning with "ch..." under the letter C in a dictionary/encyclopedia - but if it is a Czech one, you'll find them under a separate heading "CH", not even close to "C" (it is G, H, Ch, I...). IMHO this should be addressed in some future release of Czech orthography and we should declare, that "CH" is in fact two letters and standard ordering rules would apply from that time (in fact, it is long overdue... but I'm afraid no one will bother with it and we will be stuck with this "letter" forever).Tue, 25 May 2010 08:55:48 GMTVladanhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]Michael Poppers (5/25/2010)[/b][hr][quote][b]malleswarareddy_m (5/25/2010)[/b][hr]...select string from #t where string COLLATE CZECH_CI_AS like '_'the above you have given returns givesCHChch¢h four rows....[/quote]I was wondering why my SQL Query Analyzer output apparently listed "ch" twice -- thanks for listing the accurate output (in which the fourth row is not "ch"). Now try SELECT DISTINCTing the column ;-) -- the #rows should tell us precisely how many LIKE '_' characters actually exist between 'A' and 'Z' :-).[/quote]Your idea with DISTINCT is great. It makes the things little bit more funny with case-insensitive collations.Tue, 25 May 2010 08:48:17 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]malleswarareddy_m (5/25/2010)[/b][hr]...select string from #t where string COLLATE CZECH_CI_AS like '_'the above you have given returns givesCHChch¢h four rows....[/quote]I was wondering why my SQL Query Analyzer output apparently listed "ch" twice -- thanks for listing the accurate output (in which the fourth row is not "ch"). Now try SELECT DISTINCTing the column ;-) -- the #rows should tell us precisely how many LIKE '_' characters actually exist between 'A' and 'Z' :-).Tue, 25 May 2010 08:24:16 GMTMichael Poppershttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx2 crussell-931424:Fine, CZECH is funny for you. OK.Do you think it about other languages? About those with same behaviour? Eg. Traditional_Spanish - AFAIK Spanish is native language in a great part of world.Tue, 25 May 2010 07:45:38 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxThanks Honza! You know, I guessed what the answer would be just by looking at the options before I even read the question. Then I read the question and changed my mind. That will teach me for ignoring my initial gut feeling!Thanks,ChadTue, 25 May 2010 07:40:22 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxThis is a bogus question and answer. CZECH? It depends? Maybe if I was a CZECH. Get real. You might as well have said it will return 13 rows. Oh you didn't get 13 rows? That must be because you didn't realize that the text wasn't SQL. It was my own special coding language that will return no more than 13 rows unless you specify otherwise. How ridiculous, just like this answer was. It depends.Tue, 25 May 2010 07:33:21 GMTcrussell-931424http://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]skcadavre (5/25/2010)[/b][hr]I got this right by process of elimination. . . I thought it'd fail so decided to go with "it depends" :hehe:If "error" had been an option, I'd have got this wrong ;-)[/quote]Yes, error is always a possible answer.Tue, 25 May 2010 04:10:57 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxThanks to a nice Question I need to learn more about collationsTue, 25 May 2010 03:30:12 GMTsharath.chalamgarihttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]ZeroFusion (5/25/2010)[/b][hr]Actually, I'm Czech :-) But I suppose the link was meant for other readers. A quick side note: that page looks awesome![/quote]Yes, it was clear to me, you are Czech too. The sources was for other readers.I like Omnigot. It's one of my favorite sites. I don't know any other you can have translation of sentence "My hovercraft is full of eels" (http://www.omniglot.com/language/phrases/hovercraft.htm) to nearly any language (including Klingon and Volapük) :-DTue, 25 May 2010 03:22:53 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxthanks for the question... got to know something new today, didn't get the point though :(Tue, 25 May 2010 03:22:21 GMTziangijhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspxI got this right by process of elimination. . . I thought it'd fail so decided to go with "it depends" :hehe:If "error" had been an option, I'd have got this wrong ;-)Tue, 25 May 2010 02:44:49 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]honza.mf (5/25/2010)[/b][hr]If you want to know more about Czech language have a look at http://www.omniglot.com/writing/czech.htm, very well done.If you want to know more and have a fun, you can read Three Men on the Bummel by Jerome Klapka Jerome, chapter about Prague, all whole the book.[/quote]Actually, I'm Czech :-) But I suppose the link was meant for other readers. A quick side note: that page looks awesome!Tue, 25 May 2010 02:21:14 GMTZeroFusionhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]Hugo Kornelis (5/25/2010)[/b][hr](note to self: in future, check question category before answering!)[/quote]It's a good practice. It made me several times to answer correctly ;-)Tue, 25 May 2010 02:18:21 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]vk-kirov (5/25/2010)[/b][hr][quote][b]honza.mf (5/25/2010)[/b][hr]If someone discover another collation in another language with similar behaviour, I will be very glad to have such information.[/quote]Here is a script which finds that collations (you should have the table #t from the QOTD):...It returns Croatian, Czech, Danish_Norwegian, Hungarian, Slovak, Traditional_Spanish, and Vietnamese on my machine.[/quote]Thank you.Tue, 25 May 2010 02:16:55 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]ZeroFusion (5/25/2010)[/b][hr]A note for people not familiar with Czech language: in the Czech alphabet, a letter "CH" is just between letters "H" and "I".[/quote]If you want to know more about Czech language have a look at http://www.omniglot.com/writing/czech.htm, very well done.If you want to know more and have a fun, you can read Three Men on the Bummel by Jerome Klapka Jerome, chapter about Prague, all whole the book.Tue, 25 May 2010 02:14:26 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]honza.mf (5/25/2010)[/b][hr][quote][b]stewartc-708166 (5/25/2010)[/b][hr]TrickyRequires some knowledge of collations[/quote]Yes, the question was in Collations category :-P[/quote]:laugh: I had not even seen that!(note to self: in future, check question category before answering!)Tue, 25 May 2010 02:12:20 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]stewartc-708166 (5/25/2010)[/b][hr]TrickyRequires some knowledge of collations[quote][b]malleswarareddy_m (5/24/2010)[/b][hr]hi ,like operator will search for _ only.and return any characters. then it does not return any rows.because i worked many times using like operator.but if we use '%_%' or '%_' or '_%'. then i think it will display the number of characters.but directly with like '_' it will not return the rows.and also i extecuted the above query but it will not return any rows.[/quote]Remember, malleswarareddy_m, when used with a LIKE, the underscore is also a wildcard, representing "any single character"[/quote]I know that _ is also wildcard character.but when i got wrong i executed on different combinations of wild chars but not concentrated on collations.Now i find the answer.now i am clear about the answer.Tue, 25 May 2010 01:54:41 GMTmalleswarareddy_mhttp://www.sqlservercentral.com/Forums/Topic927179-1711-1.aspx[quote][b]Hugo Kornelis (5/25/2010)[/b][hr][quote][b]malleswarareddy_m (5/24/2010)[/b][hr]hi ,like operator will search for _ only.and return any characters. then it does not return any rows.because i worked many times using like operator.but if we use '%_%' or '%_' or '_%'. then i think it will display the number of characters.but directly with like '_' it will not return the rows.and also i extecuted the above query but it will not return any rows.[/quote]Did you try setting the collation to CZECH_CI_AS, as Honza indicated in the explanation?Certain Nordic collations will also consider some two-character combinations as a single character, and I expect German collations to do that as well (e.g. ss = ß, ue = ü, etc)[/quote]Hi I checked with different collationswith two queriesselect string from #t where string COLLATE CZECH_CI_AS like '_'the above you have given returns givesCHChch¢h four rows.But when i using my default collation using this queryselect string from #t where string COLLATE SQL_Latin1_General_CP1_CI_AS like '_'It does not return any results.I got wrong.Because I did not checked on different collations.You answer is correct.The Result depens upon collations.Thanks for posting good answer.learned some thing new for the day.Tue, 25 May 2010 01:51:34 GMTmalleswarareddy_m