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 Monday, December 3, 2012 10:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 7:18 PM
Points: 7,930, Visits: 9,654
Robert-378556 (12/3/2012)
SQL Kiwi (12/3/2012)
Jaroslav Pfauser (12/3/2012)
(0 row(s) affected)
SQL2008 (64-bit) SP2

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)

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.

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.



Tom
Post #1392071
Posted Monday, December 3, 2012 11:10 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 9,928, Visits: 11,208
Robert-378556 (12/3/2012)
SQL Kiwi (12/3/2012)
Jaroslav Pfauser (12/3/2012)
(0 row(s) affected)
SQL2008 (64-bit) SP2

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)

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.

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):

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;

╔═══╦═══╗
║ A ║ a ║
╠═══╬═══╣
║ Æ ║ æ ║
╚═══╩═══╝




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1392082
Posted Monday, December 3, 2012 11:15 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
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

+1

I wish I could say 'educated'... but it really was 'lucky.'

Thanks, Tom!
Post #1392086
Posted Monday, December 3, 2012 11:37 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: Tuesday, December 23, 2014 3:10 PM
Points: 3,806, Visits: 1,237
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,


"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.
Post #1392097
Posted Monday, December 3, 2012 11:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 1, 2014 9:55 AM
Points: 1,823, Visits: 905
*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...
Post #1392098
Posted Monday, December 3, 2012 12:22 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:50 PM
Points: 615, Visits: 693
I read this question completely different and ended up getting it correct anyway. I guessed two because of '[' and ']'....

I'll take it!


Aigle de Guerre!
Post #1392108
Posted Monday, December 3, 2012 1:46 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:17 PM
Points: 18,081, Visits: 16,116
Excellent Question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1392132
Posted Monday, December 3, 2012 7:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 10:54 AM
Points: 56, Visits: 61
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.
Post #1392222
Posted Tuesday, December 4, 2012 4:14 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Thanks. !?
Post #1392350
Posted Tuesday, December 4, 2012 1:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 6:46 AM
Points: 13,646, Visits: 11,524
Great but difficult question. Thanks Tom.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1392686
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse