SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tom Thomson / LIKE a vowel / Latest Posts

RE: LIKE a vowel

asifkareem — Wed, 16 Jan 2013 12:59:23 GMT

Thanks for such a thoughtful Question.

RE: LIKE a vowel

Dineshbabu — Mon, 07 Jan 2013 05:44:42 GMT

[quote][b]Hugo Kornelis (1/7/2013)[/b][hr][quote][b]Dineshbabu (1/7/2013)[/b][hr]simply I selected one to see the explaination and understand the qstn. Really i didn't understand what the qstn is delaing abt...Can anyone xplain in detail. If already xplained pls provide me the refernce..Thanks[/quote]Did you read all the posts so far in this topic?The short story - in some languages, Æ and æ are official letters. They can also be written as AE and ae. Because of that, 'Æ' LIKE '[AEIOU]' will return true - because the first two letters in the LIKE string (A and E) are considered to represent the Æ character.[/quote]Thanks Hugo.I understood something. I will try to go through all the xplainations posted here. I think, First of all i shud understand Coallate.

RE: LIKE a vowel

Hugo Kornelis — Mon, 07 Jan 2013 05:24:02 GMT

[quote][b]Dineshbabu (1/7/2013)[/b][hr]simply I selected one to see the explaination and understand the qstn. Really i didn't understand what the qstn is delaing abt...Can anyone xplain in detail. If already xplained pls provide me the refernce..Thanks[/quote]Did you read all the posts so far in this topic?The short story - in some languages, Æ and æ are official letters. They can also be written as AE and ae. Because of that, 'Æ' LIKE '[AEIOU]' will return true - because the first two letters in the LIKE string (A and E) are considered to represent the Æ character.

RE: LIKE a vowel

Dineshbabu — Mon, 07 Jan 2013 04:28:29 GMT

simply I selected one to see the explaination and understand the qstn. Really i didn't understand what the qstn is delaing abt...Can anyone xplain in detail. If already xplained pls provide me the refernce..Thanks

RE: LIKE a vowel

jdamm — Thu, 06 Dec 2012 16:37:16 GMT

At first I was a little miffed about missing the question - especially since there wasn't a hard reference. Then after reading the discussions and experimenting with Thorn and Sharp S, I came to like the question. I definitely learned a bit today that could (but probably no chance that it will) catch me in the future.Jim

RE: LIKE a vowel

Hugo Kornelis — Thu, 06 Dec 2012 16:33:16 GMT

[quote][b]L' Eomot Inversé (12/6/2012)[/b][hr]That's still missing ij and IJ (maybe Hugo will tell us whether Dutch generally uses these ligatures or has generally switched to non-ligatured representation).[/quote]When I was young, Dutch typewriters (and -when I was slightly less young- even some Dutch computer keyboards) had a seperate key for the ligature ij. Rather obviously, actually, since the ij is the 25th letter of the Dutch alphabet. And in case you wonder where we then have left the y - we don't. The y is not officially a letter of the Dutch alphabet, even though we do use it in some words, such a yoghurt. Yes, I agree that this is weird.(By the way, when I tried to find confirming sources for this on the internet, I could not. All pages I visited either claim that the y is the 25th letter, or list both ij as 25th and y as 26th letter. This is definitely NOT how I learned it in elementary school!)However, that is now a thing of the past. Nobody uses ligature ij anymore. (And frankly, in a proportional font you would not see the difference anyway!) That is probably why all internet pages about the Dutch alphabet list y as the 25th letter instead of ligature ij.[quote]German printers once used a ue ligature (originally with the e above the u, instead of to the right of it) in place of ü, but that is not used now (at least I believe not: but people with old typewriters which can't produce ü still sometimes use ue - as two separated characters, not a ligature).[/quote]As far as I know, German always considers an e after a vowel equivalent to an unlaut above that vowel - so ae, oe and ue are equivalent to ä, ö and ü. (German never uses an umlaut above an i or e, so ee and ie are just plain ee and ie).

RE: LIKE a vowel

L' Eomot Inversé — Thu, 06 Dec 2012 11:31:40 GMT

[quote][b]jdamm (12/6/2012)[/b][hr]Another two that could come up are:Þ - the archaic letter thorn - which will come up with 'th' in the select. ß - the German "Sharp S" - which will come up with "ss' in the select.A few more "undocumented features"...I'm guessing on the collation, there are lots of other "opportunities".Jim[/quote]You've pushed one of my go buttons there - languages and orthography used to be a big thing for me. (Languages still are.) So now I shall be boring, but perhaps informative.In the ISO Latin1 8-bit character set, there are only 5 ligatures: ae,th, AE,TH, and ss. Of course this means that Latin1 is missing rather a lot of ligatures which are used by languages which use "roman" characters for writing. Windows Latin1 adds oe and OE (so the French probably prefer Windows Latin1 to ISO, as these are essential for standard French spelling) , so in SQL Server with default collation (or really default code page) we see 7 ligature digraphs in the single byte character set. That's still missing ij and IJ (maybe Hugo will tell us whether Dutch generally uses these ligatures or has generally switched to non-ligatured representation). There were once other ligatures in languages that use Latin1 (or a small extension of it). Until a couple of decades ago, Spanish treated CH ch Ll and ll each as an individual character, not each as two characters; German printers once used a ue ligature (originally with the e above the u, instead of to the right of it) in place of ü, but that is not used now (at least I believe not: but people with old typewriters which can't produce ü still sometimes use ue - as two separated characters, not a ligature).There 49 (? not sure; I think it's 53 altogether, and as 4 are ligatures .... but I'm not sure 53 is right) other (not ligature) characters used by languages using variants of the roman alphabet that are not in ISO Latin1 (most of them are in Windows Latin1) which has thrown away some obsolete/redundant control characters to make room for them), but they are not ligatures so won't be picked up as matching two adjacent characters. Ten of the 48 are needed for Latin (in the orthography that has vowel length marked), which suggests that Latin1 is a bit of a misnomer.

RE: LIKE a vowel

jdamm — Thu, 06 Dec 2012 08:40:35 GMT

Another two that could come up are:Þ - the archaic letter thorn - which will come up with 'th' in the select. ß - the German "Sharp S" - which will come up with "ss' in the select.A few more "undocumented features"...I'm guessing on the collation, there are lots of other "opportunities".Jim

RE: LIKE a vowel

sipas — Wed, 05 Dec 2012 03:47:12 GMT

So to correctly test for just those characters should you use:@x IN ('a','e','i','o','u') ?Good question, an undocumented 'feature' that could lead to unexpected results.

RE: LIKE a vowel

Koen Verbeeck — Tue, 04 Dec 2012 13:48:57 GMT

Great but difficult question. Thanks Tom.

RE: LIKE a vowel

(Bob Brown) — Tue, 04 Dec 2012 04:14:38 GMT

Thanks. !?

RE: LIKE a vowel

Ken Hiatt — Mon, 03 Dec 2012 19:22:20 GMT

I've never use Collate and don't know what it does.I figured the answer would be 5. Like 5 letters excluding the upper case, would leave the 5 lower case.Not seeing that in the answer, I chose more than 3. Based on some of the comments, I could, technically, be considered correct. :-D

RE: LIKE a vowel

SQLRNNR — Mon, 03 Dec 2012 13:46:48 GMT

Excellent Question.

RE: LIKE a vowel

Meow Now — Mon, 03 Dec 2012 12:22:09 GMT

I read this question completely different and ended up getting it correct anyway. I guessed two because of '[' and ']'....I'll take it! :-P

RE: LIKE a vowel

Rich Weissler — Mon, 03 Dec 2012 11:40:09 GMT

*blush* Being wrong is teaching me even more about collations.I'm also seeing another possible advantage to unicode. Switching to unicode, it looks like NCHAR(198) and NCHAR(230) returns the same values (Æ, æ) in Latin and Czech collations. I assume it'll be the same in all collations...

RE: LIKE a vowel

EL Jerry — Mon, 03 Dec 2012 11:37:59 GMT

I set up a test script and ran it, and to my surprise got the right answer based on the script outcome.I was surprised of the results.Excellent question, Tom!! Keep them coming!Saludos,

RE: LIKE a vowel

Revenant — Mon, 03 Dec 2012 11:15:39 GMT

[quote][b]Bex (12/3/2012)[/b][hr]I'll hold my hand up and say I got this right by means of a lucky guess.Very interesting question and excellent, full answer. Thanks, Tom!Bex[/quote]+1I wish I could say 'educated'... but it really was 'lucky.'Thanks, Tom!

RE: LIKE a vowel

Paul White — Mon, 03 Dec 2012 11:10:53 GMT

[quote][b]Robert-378556 (12/3/2012)[/b][hr][quote][b]SQL Kiwi (12/3/2012)[/b][hr][quote][b]Jaroslav Pfauser (12/3/2012)[/b][hr](0 row(s) affected)SQL2008 (64-bit) SP2[/quote]It seems you wrote a test script that did not cover the full range of possibilities. Include CHAR(198) and CHAR(230) in your test.(2 row(s) affected)[/quote]Doesn't the test sql provided in answer already do that?On my server it returns 0 rows, so it appears like some other things affect this. Maybe default collation. Probably.[/quote]Oh my apologies - I didn't even read the explanation today.Of course the question should have used a SQL collation, not a Windows one that takes account of Windows language settings. Using a SQL collation would ensure that everyone used the same code page and displayed the same characters (well, almost):[code="sql"]SELECT CHAR(198) COLLATE SQL_Czech_CP1250_CI_AS, CHAR(230) COLLATE SQL_Czech_CP1250_CI_AS;SELECT CHAR(198) COLLATE SQL_Latin1_General_CP1_CI_AI, CHAR(230) COLLATE SQL_Latin1_General_CP1_CI_AI;[/code][code="plain"]╔═══╦═══╗║ A ║ a ║╠═══╬═══╣║ Æ ║ æ ║╚═══╩═══╝[/code]

RE: LIKE a vowel

L' Eomot Inversé — Mon, 03 Dec 2012 10:56:31 GMT

[quote][b]Robert-378556 (12/3/2012)[/b][hr][quote][b]SQL Kiwi (12/3/2012)[/b][hr][quote][b]Jaroslav Pfauser (12/3/2012)[/b][hr](0 row(s) affected)SQL2008 (64-bit) SP2[/quote]It seems you wrote a test script that did not cover the full range of possibilities. Include CHAR(198) and CHAR(230) in your test.(2 row(s) affected)[/quote]Doesn't the test sql provided in answer already do that?On my server it returns 0 rows, so it appears like some other things affect this. Maybe default collation. Probably.[/quote]Yes, the default collation. The test sql in the answer works where the default collation is one of the Latin1_General set, but for some other collations it doesn't. I should have specified a default collation for the context of the code as part of the answer. What 8-bit character char(I) delivers depends on the default collation for the context in which char is called, so that whether ascii(char(I)) = I (I a non-negative integer less than 256) depends on what I is and what the default collation is. Personally I think that's unfortunate, but that's how char operates.

RE: LIKE a vowel

sestell1 — Mon, 03 Dec 2012 09:42:53 GMT

Very interesting question... definitely not something I would have realized otherwise.

RE: LIKE a vowel

Rob Schripsema — Mon, 03 Dec 2012 09:33:28 GMT

Great question...but a challenge for a Monday morning!

RE: LIKE a vowel

Rich Weissler — Mon, 03 Dec 2012 09:13:08 GMT

[quote]On my server it returns 0 rows, so it appears like some other things affect this. Maybe default collation. Probably.[/quote]I'd suspect a different set of Extended ASCII characters, probably not the default collation on the server. For the folks who are getting no records back, what language is the machine running?[appending] Yeah, it looks like several of the ASCII character sets don't include those AE/ae characters. http://en.wikipedia.org/wiki/ISO/IEC_8859

RE: LIKE a vowel

Robert-378556 — Mon, 03 Dec 2012 08:50:59 GMT

[quote][b]SQL Kiwi (12/3/2012)[/b][hr][quote][b]Jaroslav Pfauser (12/3/2012)[/b][hr](0 row(s) affected)SQL2008 (64-bit) SP2[/quote]It seems you wrote a test script that did not cover the full range of possibilities. Include CHAR(198) and CHAR(230) in your test.(2 row(s) affected)[/quote]Doesn't the test sql provided in answer already do that?On my server it returns 0 rows, so it appears like some other things affect this. Maybe default collation. Probably.

RE: LIKE a vowel

Paul White — Mon, 03 Dec 2012 07:52:07 GMT

[quote][b]Jaroslav Pfauser (12/3/2012)[/b][hr](0 row(s) affected)SQL2008 (64-bit) SP2[/quote]It seems you wrote a test script that did not cover the full range of possibilities. Include CHAR(198) and CHAR(230) in your test.(2 row(s) affected)

RE: LIKE a vowel

Jaroslav Pfauser — Mon, 03 Dec 2012 07:45:40 GMT

(0 row(s) affected)SQL2008 (64-bit) SP2

RE: LIKE a vowel

Paul White — Mon, 03 Dec 2012 07:40:45 GMT

Super question.

RE: LIKE a vowel

Lon-860191 — Mon, 03 Dec 2012 07:37:50 GMT

Not a lucky guess, but a good question none the less. After reviewing the answer and explanation, I learned something, probably nothing I can use today, but who knows where the future lies.Thanks,Lon

RE: LIKE a vowel

Rich Weissler — Mon, 03 Dec 2012 06:36:46 GMT

I got it wrong, but have been playing with the query. If I switch from char(1) values to nchar(1) and search 65535 values, it spits out six values:Æ 198æ 230Ǣ 482ǣ 483Ǽ 508ǽ 509---I don't see any other accented versions of Œ 338, and œ 339.Excellent. And there are double letters for dz (two different forms), lj, nj too.Fascinating.

RE: LIKE a vowel

Toreador — Mon, 03 Dec 2012 04:55:01 GMT

[quote][b]philip.cullingworth (12/3/2012)[/b][hr]For interest, I thought I'd have a bit more of a look at which options displayed this effect, what caused Æ and æ to be returned.It appears on our server (SQL 2005) that it is having a and e together in the like statement that causes this.Changing the first like returned the following results (keeping the collation as in the question): [ae] returns Æ and æ [eaiou] returns nothing [uioea] returns Œ(140) and œ(156)It appears that SQL is doing more than checking for just the characters entered.Philip[/quote]It's obvious now I've read that!Thanks for the explanation, and thanks to Tom for the question :-)

RE: LIKE a vowel

Mark-101232 — Mon, 03 Dec 2012 04:41:22 GMT

Interesting question, I got it wrong.There seem to be another couple of ligature characters that show this sort of behaviour. Character 153 (TM) doesn't though[code="sql"]SELECT CHAR(140) AS OE, CASE WHEN CHAR(140) LIKE '[O]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[O]', CASE WHEN CHAR(140) LIKE '[E]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[E]', CASE WHEN CHAR(140) LIKE '[OE]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[OE]', CASE WHEN CHAR(140) LIKE '[EO]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[EO]'SELECT CHAR(156) AS oe, CASE WHEN CHAR(156) LIKE '[O]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[O]', CASE WHEN CHAR(156) LIKE '[E]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[E]', CASE WHEN CHAR(156) LIKE '[OE]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[OE]', CASE WHEN CHAR(156) LIKE '[EO]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[EO]'SELECT CHAR(198) AS AE, CASE WHEN CHAR(198) LIKE '[A]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[A]', CASE WHEN CHAR(198) LIKE '[E]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[E]', CASE WHEN CHAR(198) LIKE '[AE]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[AE]', CASE WHEN CHAR(198) LIKE '[EA]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[EA]'SELECT CHAR(230) AS ae, CASE WHEN CHAR(230) LIKE '[A]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[A]', CASE WHEN CHAR(230) LIKE '[E]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[E]', CASE WHEN CHAR(230) LIKE '[AE]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[AE]', CASE WHEN CHAR(230) LIKE '[EA]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[EA]'SELECT CHAR(153) AS TM, CASE WHEN CHAR(153) LIKE '[T]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[T]', CASE WHEN CHAR(153) LIKE '[M]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[M]', CASE WHEN CHAR(153) LIKE '[TM]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[TM]', CASE WHEN CHAR(153) LIKE '[MT]' COLLATE LATIN1_GENERAL_CI_AI THEN 'true' ELSE 'false' END AS '[MT]'[/code]

RE: LIKE a vowel

call.copse — Mon, 03 Dec 2012 03:01:57 GMT

I got it correct - yay I'm in the 8%! - but I didn't work it out through mental struggle, rather I wrote a test script. So I'm not clever enough to have sussed it, but at least I knew that fact.

RE: LIKE a vowel

Stewart "Arturius" Campbell — Mon, 03 Dec 2012 03:00:59 GMT

This is a very interesting question, Thanks Tom

RE: LIKE a vowel

Robert-378556 — Mon, 03 Dec 2012 02:46:53 GMT

I checked this: @@version=[quote]Microsoft SQL Server 2008 R2 (SP1) - 10.50.2811.0 (X64) Apr 6 2012 01:59:29 Copyright (c) Microsoft Corporation Data Center Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) [/quote]Should be the same version as verified by author of this question.Result of test sql is 0 values as expected. Regarding other comments it really appears like asking people what will be the outcome of a random number function. ;-)

RE: LIKE a vowel

Mighty — Mon, 03 Dec 2012 02:31:37 GMT

The questions seemed straight forward, and the outcome should have been 0 results (according to me) since it seemed to say some like [font="Courier New"]WHERE @x LIKE '[aeiou]' AND @x NOT LIKE '[aeiou]'[/font]. Wrong!So now it gets interesting why, and eventually I can tell the reason: in [font="Courier New"]LIKE '[aeiou]'[/font], the [b]ae[/b] bit is also interpreted as one character. If the like would have been [font="Courier New"]LIKE '[aeioeu]'[/font], you would get 4 result ([b]oe[/b] also one character).So it was an interesting outcome of something that seemed straight forward, but a bit week that there was no reason why the answer was like that. Doesn't seem to be a good base an giving people a point or not. Like asking people what the outcome will be of a random number function... ;-)

RE: LIKE a vowel

philip.cullingworth — Mon, 03 Dec 2012 02:25:43 GMT

For interest, I thought I'd have a bit more of a look at which options displayed this effect, what caused Æ and æ to be returned.It appears on our server (SQL 2005) that it is having a and e together in the like statement that causes this.Changing the first like returned the following results (keeping the collation as in the question): [ae] returns Æ and æ [eaiou] returns nothing [uioea] returns Œ(140) and œ(156)It appears that SQL is doing more than checking for just the characters entered.Philip

RE: LIKE a vowel

demonfox — Mon, 03 Dec 2012 02:17:38 GMT

[quote][b]Bex (12/3/2012)[/b][hr]I'll hold my hand up and say I got this right by means of a lucky guess.Very interesting question and excellent, full answer. Thanks, Tom!Bex[/quote]I got it correct by an unlucky guess; interest question though..I guess I need more research.

RE: LIKE a vowel

Lokesh Vij — Mon, 03 Dec 2012 01:27:38 GMT

An interesting question. Put in a lot of thinking before attempting it and got it wrong in the end :-)

RE: LIKE a vowel

Bex — Mon, 03 Dec 2012 01:21:50 GMT

I'll hold my hand up and say I got this right by means of a lucky guess.Very interesting question and excellent, full answer. Thanks, Tom!Bex

LIKE a vowel

L' Eomot Inversé — Sat, 01 Dec 2012 12:14:12 GMT

Comments posted to this topic are about the item [B]LIKE a vowel[/B]