LIKE a vowel
-
Comments posted to this topic are about the item LIKE a vowel
Tom
-
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
-
An interesting question. Put in a lot of thinking before attempting it and got it wrong in the end 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Bex (12/3/2012)
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
I got it correct by an unlucky guess;
interest question though..I guess I need more research.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
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
-
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 ae bit is also interpreted as one character. If the like would have been [font="Courier New"]LIKE '[aeioeu]'[/font], you would get 4 result (oe 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... 😉
-
I checked this: @@version=
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)
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. 😉
-
This was removed by the editor as SPAM
-
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.
-
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
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]'
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
philip.cullingworth (12/3/2012)
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
It's obvious now I've read that!
Thanks for the explanation, and thanks to Tom for the question 🙂
-
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.
-
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
-
Super question.
-
(0 row(s) affected)
SQL2008 (64-bit) SP2
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply