Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LIKE a vowel


LIKE a vowel

Author
Message
Toreador
Toreador
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2423 Visits: 8064
philip.cullingworth (12/3/2012)
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


It's obvious now I've read that!

Thanks for the explanation, and thanks to Tom for the question :-)
Rich Weissler
Rich Weissler
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2017 Visits: 954
I got it wrong, but have been playing with the query. If I switch from char(1) values to nchar(1) and search 65535 values, it spits out six values:
Æ 198
æ 230
Ǣ 482
ǣ 483
Ǽ 508
ǽ 509
---
I don't see any other accented versions of Œ 338, and œ 339.

Excellent. And there are double letters for dz (two different forms), lj, nj too.

Fascinating.
Lon-860191
Lon-860191
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 278
Not a lucky guess, but a good question none the less. After reviewing the answer and explanation, I learned something, probably nothing I can use today, but who knows where the future lies.
Thanks,
Lon
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10352 Visits: 11350
Super question.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Jaroslav Pfauser
Jaroslav Pfauser
Say Hey Kid
Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)

Group: General Forum Members
Points: 710 Visits: 100
(0 row(s) affected)

SQL2008 (64-bit) SP2
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10352 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Robert-378556
Robert-378556
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1570 Visits: 1007
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
Rich Weissler
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2017 Visits: 954
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
Rob Schripsema
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2321 Visits: 11042
Great question...but a challenge for a Monday morning!

Rob Schripsema
Propack, Inc.
sestell1
sestell1
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2209 Visits: 3418
Very interesting question... definitely not something I would have realized otherwise.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search