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 «««23456»»

Simple LIKE with wildcard Expand / Collapse
Author
Message
Posted Tuesday, May 25, 2010 2:40 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 7:41 PM
Points: 634, Visits: 399
honza.mf (5/25/2010)
If someone uses case sensitive collation, he will have all characters in uppercase.

Sorry, ignore this reply and see my next reply . Thanks.
Post #927806
Posted Tuesday, May 25, 2010 2:43 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 7:41 PM
Points: 634, Visits: 399
honza.mf (5/25/2010)
If someone uses case sensitive collation, he will have all characters in uppercase.

Sounds reasonable, but use COLLATE Czech_CS_AS (instead of the case-insensitive collation) -- SELECT DISTINCT will return three rows (instead of one), and one of the result rows appears (to my non-Czech eyes) to not be what in the Roman alphabet would be called "uppercase"!
Post #927809
Posted Tuesday, May 25, 2010 2:47 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 744, Visits: 1,125
honza.mf (5/25/2010)
jlennartz (5/25/2010)

If all characters were inserted into DB as upper case how come query is returning lower case?

It's another aspect of this question not mentioned yet: All characters inserted into are not guaranted to be uppercase!
You feel they are uppercase due to "while @a2 <= 'Z'" condition. You must evaluate this condition in appropriate collation too. Most of us use case-insensitive collations and in such collations lowercase letters also fit this condition. And it si true for the special characters between 'Z' and 'a' in ASCII table like '[', '\', ']' - it's not obvious but for me it's clear. There can be even more characters that fit, it's collation dependant.
If someone uses case sensitive collation, he will have all characters in uppercase.


Thank you for the answer it make sense, now. You won't get A-Z but whatever the Check equivqlent of their ascii number is.

Jerry
Post #927814
Posted Tuesday, May 25, 2010 2:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
honza.mf (5/25/2010)
...
I don't know our linguists (ÚPJČ) but this will be very hard to do. Orthographic rules are very rigid and cannot be changed so easy. I remember last great changes in czech orthography or several years ago changes Germans made. It was always painful.


First of all: very nice question!! I just "lost" one point, but learned something new. I was aware of the collation issue but I thought 'A' to 'Z' didn't make room for it.

Regarding our orthography changes (I'm German): at least it allows me to write the way I'd like to: most probably there was/is/will_be a time and place where the spelling I use was/is/will_be valid
But it definitely makes it really hard to help your kids through (school) homework... Ooops, getting off topic. Sorry. Again: Nice QOTD!




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #927820
Posted Wednesday, May 26, 2010 12:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 1,298, Visits: 1,306
Michael Poppers (5/25/2010)
honza.mf (5/25/2010)
If someone uses case sensitive collation, he will have all characters in uppercase.

Sounds reasonable, but use COLLATE Czech_CS_AS (instead of the case-insensitive collation) -- SELECT DISTINCT will return three rows (instead of one), and one of the result rows appears (to my non-Czech eyes) to not be what in the Roman alphabet would be called "uppercase"!

Sorry, it was my mistake as I haven't checked it. Case sensitivity of a collation is used probably in equality comparisons only. Loops in example are controled by less or equal operator, and it says 'a' <= 'Z'




See, understand, learn, try, use efficient
© Dr.Plch
Post #927960
Posted Wednesday, May 26, 2010 10:46 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499
honza.mf (5/25/2010)
jlennartz (5/25/2010)

If all characters were inserted into DB as upper case how come query is returning lower case?

It's another aspect of this question not mentioned yet: All characters inserted into are not guaranted to be uppercase!
You feel they are uppercase due to "while @a2 <= 'Z'" condition. You must evaluate this condition in appropriate collation too. Most of us use case-insensitive collations and in such collations lowercase letters also fit this condition. And it si true for the special characters between 'Z' and 'a' in ASCII table like '[', '\', ']' - it's not obvious but for me it's clear. There can be even more characters that fit, it's collation dependant.
If someone uses case sensitive collation, he will have all characters in uppercase.

Quite correct. The actual number of rows in #t with a default installation is 20449. To get the 26 by 26 result requires
while ASCII(@a1) &lt;= ASCII('Z') begin

, etc.

This QotD turned out to be much more interesting than I initially thought it would.

Thanks for the lesson!


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #928458
Posted Wednesday, May 26, 2010 12:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 11,168, Visits: 10,930
Very nice question today. I only found it easy because I have been doing stuff with unusual collations recently.
There's a great set of visual tables showing sort (and comparison) orders at:
http://www.collation-charts.org/mssql/

edit: used a code tag instead of url first time around




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #928508
Posted Wednesday, May 26, 2010 12:20 PM
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: Thursday, April 10, 2014 7:08 AM
Points: 3,448, Visits: 4,406
Tom Garth (5/26/2010)
The actual number of rows in #t with a default installation is 20449.

It depends I've got 3969 rows with my default installation.
Post #928518
Posted Wednesday, May 26, 2010 12:25 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 1,298, Visits: 1,306
Tom Garth (5/26/2010)

Quite correct. The actual number of rows in #t with a default installation is 20449. To get the 26 by 26 result requires
while ASCII(@a1) &lt;= ASCII('Z') begin

, etc.

This QotD turned out to be much more interesting than I initially thought it would.

Thanks for the lesson!

Yes, it was very interesting for me in the moment I dicovered the problem with 'CH'. Other interesting thoughts came to me when I wrote the question and the discussion is very useful.




See, understand, learn, try, use efficient
© Dr.Plch
Post #928523
Posted Wednesday, May 26, 2010 12:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 1,298, Visits: 1,306
Paul White NZ (5/26/2010)
Very nice question today. I only found it easy because I have been doing stuff with unusual collations recently.
There's a great set of visual tables showing sort (and comparison) orders at:
http://www.collation-charts.org/mssql/

edit: used a code tag instead of url first time around

Thank you for URL. It's very good source. It explained me a lot about some strange things I met before.




See, understand, learn, try, use efficient
© Dr.Plch
Post #928527
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse