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)
If you like to set a collation for this script, you can use create table #t (string varchar (2) COLLATE XYZ)
(XYZ can be Czech_CI_AS or some other) in the first line and change the insert statement to insert into #t select (@a1 + @a2) COLLATE XYZ
to avoid collation confilct.
It is possible to surround full script by cursor that will enumerate all collations available, but I must say I was to lazy to write it.
I had an idea of German collations to work similar way, but not. Those, I have tried, returned no rows.
If someone discover another collation in another language with similar behaviour, I will be very glad to have such information.