LIKE a vowel

Question

Post reply

LIKE a vowel

TomThomson

SSC Guru

Points: 104773
More actions
December 1, 2012 at 12:14 pm

#393719

Comments posted to this topic are about the item LIKE a vowel
Tom
December 3, 2012 at 3:01 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Bex SSCrazy Points: 2437 More actions · Answer 1

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

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 questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

demonfox SSCertifiable Points: 6289 More actions · Answer 3

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:

philip.cullingworth SSCrazy Points: 2150 More actions · Answer 4

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

Mighty SSCrazy Eights Points: 9063 More actions · Answer 5

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... 😉

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 6

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. 😉

call.copse SSCoach Points: 17957 More actions · Answer 7

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.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 8

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

Toreador SSChampion Points: 11358 More actions · Answer 9

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 🙂

Rich Weissler Hall of Fame Points: 3286 More actions · Answer 10

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.

Lon-860191 SSCrazy Points: 2449 More actions · Answer 11

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

Paul White SSC Guru Points: 150467 More actions · Answer 12

Super question.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jaroslav Pfauser SSC Eights! Points: 826 More actions · Answer 13

Jaroslav Pfauser

SSC Eights!

Points: 826

December 3, 2012 at 7:45 am

#1564886

(0 row(s) affected)

SQL2008 (64-bit) SP2