LIKE a vowel

Question

LIKE a vowel

Viewing 15 posts - 16 through 30 (of 38 total)

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

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

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)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

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.

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

On my server it returns 0 rows, so it appears like some other things affect this. Maybe default collation. Probably.

I'd suspect a different set of Extended ASCII characters, probably not the default collation on the server. For the folks who are getting no records back, what language is the machine running?

[appending] Yeah, it looks like several of the ASCII character sets don't include those AE/ae characters. http://en.wikipedia.org/wiki/ISO/IEC_8859

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 4

Great question...but a challenge for a Monday morning!

Rob Schripsema
Propack, Inc.

sestell1 SSChampion Points: 10230 More actions · Answer 5

Very interesting question... definitely not something I would have realized otherwise.

TomThomson SSC Guru Points: 104773 More actions · Answer 6

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

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

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Revenant SSC-Forever Points: 42467 More actions · Answer 8

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!

EL Jerry SSCertifiable Points: 7063 More actions · Answer 9

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.[/url]

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

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

Kick6Tiger SSCrazy Points: 2380 More actions · Answer 11

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!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

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[/url]
Learn Extended Events

Ken Hiatt SSC-Addicted Points: 469 More actions · Answer 13

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

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 14

(Bob Brown)

SSCrazy

Points: 2705

December 4, 2012 at 4:14 am

#1565207

Thanks. !?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 15

Great but difficult question. Thanks Tom.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP