SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple LIKE with wildcard


Simple LIKE with wildcard

Author
Message
Michael Poppers
Michael Poppers
SSC Eights!
SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)

Group: General Forum Members
Points: 827 Visits: 416
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.
Michael Poppers
Michael Poppers
SSC Eights!
SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)

Group: General Forum Members
Points: 827 Visits: 416
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"!
jlennartz
jlennartz
SSC Eights!
SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)

Group: General Forum Members
Points: 830 Visits: 1197
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
LutzM
LutzM
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: 10263 Visits: 13559
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
honza.mf
honza.mf
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: 1931 Visits: 1323
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
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1239 Visits: 1499
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) <= 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

Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15544 Visits: 11354
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 Rolleyes



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3696 Visits: 4408
Tom Garth (5/26/2010)
The actual number of rows in #t with a default installation is 20449.

It depends :-D I've got 3969 rows with my default installation.
honza.mf
honza.mf
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: 1931 Visits: 1323
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) <= 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
honza.mf
honza.mf
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: 1931 Visits: 1323
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 Rolleyes

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