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 03, 2012 4:55 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 1,659, Visits: 6,008
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
Post #1391855
Posted Monday, December 03, 2012 6:36 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 07, 2014 5:50 AM
Points: 1,792, Visits: 890
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.
Post #1391907
Posted Monday, December 03, 2012 7:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:30 AM
Points: 1,310, Visits: 246
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
Post #1391946
Posted Monday, December 03, 2012 7:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
Super question.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1391949
Posted Monday, December 03, 2012 7:45 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: Monday, April 08, 2013 8:13 AM
Points: 700, Visits: 100
(0 row(s) affected)

SQL2008 (64-bit) SP2
Post #1391952
Posted Monday, December 03, 2012 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1391959
Posted Monday, December 03, 2012 8:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:21 PM
Points: 1,149, Visits: 871
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.
Post #1392005
Posted Monday, December 03, 2012 9:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 07, 2014 5:50 AM
Points: 1,792, Visits: 890
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
Post #1392018
Posted Monday, December 03, 2012 9:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:46 AM
Points: 1,906, Visits: 10,945
Great question...but a challenge for a Monday morning!


Rob Schripsema
Accelitec, Inc
Post #1392028
Posted Monday, December 03, 2012 9:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 1,195, Visits: 2,038
Very interesting question... definitely not something I would have realized otherwise.
Post #1392038
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse