Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

LIKE a vowel Expand / Collapse
Author
Message
Posted Saturday, December 1, 2012 12:14 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 8,726, Visits: 9,277
Comments posted to this topic are about the item LIKE a vowel

Tom
Post #1391658
Posted Monday, December 3, 2012 1:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:53 AM
Points: 1,187, Visits: 1,535
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
Posted Monday, December 3, 2012 1:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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
Guidelines for answers on Performance questions

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

Follow me @Twitter

Post #1391802
Posted Monday, December 3, 2012 2:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1391810
Posted Monday, December 3, 2012 2:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:47 AM
Points: 998, Visits: 845
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
Posted Monday, December 3, 2012 2:31 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 3,237, Visits: 1,267
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...

Post #1391814
Posted Monday, December 3, 2012 2:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:05 PM
Points: 1,205, Visits: 889
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.
Post #1391819
Posted Monday, December 3, 2012 3:00 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 3,917, Visits: 5,109
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
Posted Monday, December 3, 2012 3:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:07 AM
Points: 1,664, Visits: 1,091
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
Posted Monday, December 3, 2012 4:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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 »

Add to briefcase 1234»»»

Permissions Expand / Collapse