Simple LIKE with wildcard

Question

Post reply

Simple LIKE with wildcard

Viewing 11 posts - 46 through 55 (of 55 total)

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

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

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

vk-kirov SSCertifiable Points: 7686 More actions · Answer 2

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.

honza.mf SSCertifiable Points: 5519 More actions · Answer 3

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 requireswhile 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 SSCertifiable Points: 5519 More actions · Answer 4

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

gserdijn Hall of Fame Points: 3280 More actions · Answer 5

First thought: How can values like 'AA' be found with LIKE '_' ?

Zero rows. Click. Zero points.

Next time I will wait for the caffeine to do its work. I learned something, hope I remember it when I need it. 😉

Dutch Anti-RBAR League

honza.mf SSCertifiable Points: 5519 More actions · Answer 6

gserdijn (5/27/2010)
First thought: How can values like 'AA' be found with LIKE '_' ?
Zero rows. Click. Zero points.
Next time I will wait for the caffeine to do its work. I learned something, hope I remember it when I need it. 😉

I was surprised too. I watched it after one of my coworkers came with a question: "How it is possible, this query returned this data?" His pattern was little bit more complicated, something like '__[_]_[_][[]%'.

And the best point: I could write a question with "It depends" option:-)

See, understand, learn, try, use efficient
© Dr.Plch

UMG Developer SSChampion Points: 13482 More actions · Answer 7

I almost picked "It Depends" because, but I thought no, it couldn't be possible with a standard VARCHAR. Learned another thing today, at the cost of a point. 🙂

TomThomson SSC Guru Points: 104773 More actions · Answer 8

A beautiful question, which may have made some of the backwoodsmen notice that there are collations other than the one which fits US ASCII perfectly and fits nothing else. But I'm not holding my breath while waiting for the average US (or should that be "U/S"?) DBA or DB designer to notice our weird "foreign" collation stuff.

Tom

honza.mf SSCertifiable Points: 5519 More actions · Answer 9

Tom.Thomson (9/22/2010)
A beautiful question, which may have made some of the backwoodsmen notice that there are collations other than the one which fits US ASCII perfectly and fits nothing else. But I'm not holding my breath while waiting for the average US (or should that be "U/S"?) DBA or DB designer to notice our weird "foreign" collation stuff.

Thank you.

scottm30 Ten Centuries Points: 1094 More actions · Answer 10

Great question, thanks!

I lost a point here, but it was well worth it as I hadn't even considered collation when I answered.

TomThomson SSC Guru Points: 104773 More actions · Answer 11

crussell-931424 (6/11/2014)
How did I miss this one?

Judging by your comment posted Tuesday, May 25, 2010 2:33 PM the answer to that question is pretty obvious, but in the interests of being polite I will omit supplying that answer.

Tom