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 3 of 4

«««1 2 3 4 »»

LIKE a vowel

Rate Topic

Display Mode

Topic Options

Author

Message

L' Eomot Inversé

Posted Monday, December 03, 2012 10:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 7,087, Visits: 7,141

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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #1392071

Paul White

Posted Monday, December 03, 2012 11:10 AM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

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

Revenant

Posted Monday, December 03, 2012 11:15 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 3,454, Visits: 2,529

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

EL Jerry

Posted Monday, December 03, 2012 11:37 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 02, 2013 3:45 PM
Points: 3,748, Visits: 928

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

Post #1392097

Rich Weissler

Posted Monday, December 03, 2012 11:40 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 1,565, Visits: 769

*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

Meow Now

Posted Monday, December 03, 2012 12:22 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:15 PM
Points: 471, Visits: 485

I read this question completely different and ended up getting it correct anyway. I guessed two because of '[' and ']'....

I'll take it! Tongue

Aigle de Guerre!

Post #1392108

SQLRNNR

Posted Monday, December 03, 2012 1:46 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

Excellent Question.

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

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1392132

Ken Hiatt

Posted Monday, December 03, 2012 7:22 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, December 06, 2012 4:37 PM
Points: 56, Visits: 60

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