LIKE a vowel

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Tom Thomson » LIKE a vowel

39 posts, Page 1 of 4

1 2 3 4 »»»

LIKE a vowel

Rate Topic

Display Mode

Topic Options

Author

Message

L' Eomot Inversé

Posted Saturday, December 01, 2012 12:14 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:10 PM
Points: 7,185, Visits: 7,285

Comments posted to this topic are about the item LIKE a vowel

Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

http://es.linkedin.com/in/tomthomsonsoftware

Post #1391658

Bex

Posted Monday, December 03, 2012 1:21 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, June 14, 2013 4:16 AM
Points: 1,121, Visits: 1,410

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

Post #1391799

Lokesh Vij

Posted Monday, December 03, 2012 1:27 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:01 AM
Points: 1,233, Visits: 1,374

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

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1391802

demonfox

Posted Monday, December 03, 2012 2:17 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 1,092, Visits: 1,123

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

Post #1391810

philip.cullingworth

Posted Monday, December 03, 2012 2:25 AM

Right there with Babe

Group: General Forum Members
Last Login: Yesterday @ 1:04 AM
Points: 771, Visits: 723

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

Post #1391813

Mighty

Posted Monday, December 03, 2012 2:31 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:40 AM
Points: 2,747, Visits: 991

The questions seemed straight forward, and the outcome should have been 0 results (according to me) since it seemed to say some like WHERE @x LIKE '[aeiou]' AND @x NOT LIKE '[aeiou]'. Wrong!
So now it gets interesting why, and eventually I can tell the reason: in LIKE '[aeiou]', the ae bit is also interpreted as one character. If the like would have been LIKE '[aeioeu]', 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... Wink

Post #1391814

Robert-378556

Posted Monday, December 03, 2012 2:46 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 31, 2013 3:26 PM
Points: 1,080, Visits: 850

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

Post #1391819

Stewart "Arturius" Campbell

Posted Monday, December 03, 2012 3:00 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 3,164, Visits: 4,344

This is a very interesting question, Thanks Tom

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1391821

call.copse

Posted Monday, December 03, 2012 3:01 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 1,114, Visits: 717

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.

Post #1391823

Mark-101232

Posted Monday, December 03, 2012 4:41 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 2:47 AM
Points: 1,533, Visits: 18,468

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]'

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #1391850

« Prev Topic | Next Topic »

39 posts, Page 1 of 4

1 2 3 4 »»»

Permissions