Simple LIKE with wildcard

Question

Simple LIKE with wildcard

Viewing 15 posts - 16 through 30 (of 55 total)

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

ZeroFusion Old Hand Points: 326 More actions · Answer 1

honza.mf (5/25/2010)
You are right, it works.
Are you really doing it? I must say, sometimes I change collation from case-insensitive to case-sensitive, but I have never used BIN type collation yet.

Well, as I said, I just learned how LIKE behaves with Czech_CI_AS collation, so no, I haven't used it yet. However, I use Czech_BIN when I need to order Czech strings (with Czech collation) in order of English alphabet:

CREATE TABLE #t ([string] VARCHAR (5));

INSERT INTO #t ([string])

VALUES ('ACH');

INSERT INTO #t ([string])

VALUES ('ACY');

INSERT INTO #t ([string])

VALUES ('AHHC');

-- Output: 1. ACY, 2. AHHC, 3. ACH

SELECT

Row_Number() OVER (ORDER BY [string]), [string]

FROM

#t;

-- Output: 1. ACH, 2. ACY, 3. AHHC

SELECT

Row_Number() OVER (ORDER BY [string] COLLATE Czech_BIN), [string]

FROM

#t;

DROP TABLE #t;

A note for people not familiar with Czech language: in the Czech alphabet, a letter "CH" is just between letters "H" and "I".

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 2

Hugo Kornelis (5/25/2010)
malleswarareddy_m (5/24/2010)
hi ,
like operator will search for _ only.and return any characters. then it does not return any rows.because i worked many times using like operator.but if we use '%_%' or '%_' or '_%'. then i think it will display the number of characters.but directly with like '_' it will not return the rows.and also i extecuted the above query but it will not return any rows.
Did you try setting the collation to CZECH_CI_AS, as Honza indicated in the explanation?
Certain Nordic collations will also consider some two-character combinations as a single character, and I expect German collations to do that as well (e.g. ss = ß, ue = ü, etc)

Hi I checked with different collations

with two queries

select string from #t where string COLLATE CZECH_CI_AS like '_'

the above you have given returns gives

CH

Ch

ch

¢h

four rows.

But when i using my default collation using this query

select string from #t where string COLLATE SQL_Latin1_General_CP1_CI_AS like '_'

It does not return any results.I got wrong.Because I did not checked on different collations.You answer is correct.The Result depens upon collations.Thanks for posting good answer.

learned some thing new for the day.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 3

stewartc-708166 (5/25/2010)
Tricky
Requires some knowledge of collations
malleswarareddy_m (5/24/2010)
hi ,
like operator will search for _ only.and return any characters. then it does not return any rows.because i worked many times using like operator.but if we use '%_%' or '%_' or '_%'. then i think it will display the number of characters.but directly with like '_' it will not return the rows.and also i extecuted the above query but it will not return any rows.
Remember, malleswarareddy_m, when used with a LIKE, the underscore is also a wildcard, representing "any single character"

I know that _ is also wildcard character.but when i got wrong i executed on different combinations of wild chars but not concentrated on collations.Now i find the answer.now i am clear about the answer.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

honza.mf (5/25/2010)
stewartc-708166 (5/25/2010)
Tricky
Requires some knowledge of collations
Yes, the question was in Collations category 😛

:laugh: I had not even seen that!

(note to self: in future, check question category before answering!)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

ZeroFusion (5/25/2010)
A note for people not familiar with Czech language: in the Czech alphabet, a letter "CH" is just between letters "H" and "I".

If you want to know more about Czech language have a look at http://www.omniglot.com/writing/czech.htm, very well done.

If you want to know more and have a fun, you can read Three Men on the Bummel by Jerome Klapka Jerome, chapter about Prague, all whole the book.

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

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

vk-kirov (5/25/2010)
honza.mf (5/25/2010)
If someone discover another collation in another language with similar behaviour, I will be very glad to have such information.
Here is a script which finds that collations (you should have the table #t from the QOTD):
...
It returns Croatian, Czech, Danish_Norwegian, Hungarian, Slovak, Traditional_Spanish, and Vietnamese on my machine.

Thank you.

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

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

Hugo Kornelis (5/25/2010)
(note to self: in future, check question category before answering!)

It's a good practice. It made me several times to answer correctly 😉

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

ZeroFusion Old Hand Points: 326 More actions · Answer 8

honza.mf (5/25/2010)
If you want to know more about Czech language have a look at http://www.omniglot.com/writing/czech.htm, very well done.
If you want to know more and have a fun, you can read Three Men on the Bummel by Jerome Klapka Jerome, chapter about Prague, all whole the book.

Actually, I'm Czech 🙂 But I suppose the link was meant for other readers. A quick side note: that page looks awesome!

Cadavre SSC-Forever Points: 41690 More actions · Answer 9

I got this right by process of elimination. . . I thought it'd fail so decided to go with "it depends" :hehe:

If "error" had been an option, I'd have got this wrong 😉

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ziangij SSCrazy Eights Points: 8066 More actions · Answer 10

thanks for the question... got to know something new today, didn't get the point though 🙁

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

ZeroFusion (5/25/2010)
Actually, I'm Czech 🙂 But I suppose the link was meant for other readers. A quick side note: that page looks awesome!

Yes, it was clear to me, you are Czech too. The sources was for other readers.

I like Omnigot. It's one of my favorite sites. I don't know any other you can have translation of sentence "My hovercraft is full of eels" (http://www.omniglot.com/language/phrases/hovercraft.htm) to nearly any language (including Klingon and Volapük) 😀

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 12

Thanks to a nice Question

I need to learn more about collations

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

skcadavre (5/25/2010)
I got this right by process of elimination. . . I thought it'd fail so decided to go with "it depends" :hehe:
If "error" had been an option, I'd have got this wrong 😉

Yes, error is always a possible answer.

Chad Crawford SSChampion Points: 11610 More actions · Answer 14

Thanks Honza! You know, I guessed what the answer would be just by looking at the options before I even read the question. Then I read the question and changed my mind. That will teach me for ignoring my initial gut feeling!

Thanks,

Chad

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

2 crussell-931424:

Fine, CZECH is funny for you. OK.

Do you think it about other languages? About those with same behaviour? Eg. Traditional_Spanish - AFAIK Spanish is native language in a great part of world.