May 24, 2010 at 10:19 pm
Comments posted to this topic are about the item Simple LIKE with wildcard
May 24, 2010 at 10:55 pm
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.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
May 25, 2010 at 12:01 am
Nice question.
Taught me once again that Microsoft has some serious dope available. Doing some semiunicode shit into a normal varchar... damn.
Even more funny. Doing a Len on this gives 2. So SQL still considers this to be 2 chars. But treats it as 1... hrmm.
May 25, 2010 at 12:15 am
tommyh (5/25/2010)
Nice question.Taught me once again that Microsoft has some serious dope available. Doing some semiunicode shit into a normal varchar... damn.
Even more funny. Doing a Len on this gives 2. So SQL still considers this to be 2 chars. But treats it as 1... hrmm.
I think it'not semiunicode, it's a funny behviour. It's two characters, but like comparison treat it as one.
When I've seen it, I was struck.
Another funny thing is, that this behaviour can be seen only with "Windows" collation. If I have a server with old unsupported SQL collation, it works normally.
May 25, 2010 at 12:34 am
Great question!
I did consider collations before replying, but only thinking about the possibility of a collation where the space character would fall somewhere between 'A' and 'Z'. And after discarding that option, I picked the most popular answer: 0.
Point lost, nothing learned - I was already aware of this collation-specific behaviour, but failed to consider it before replying.
Thanks, Honza!
May 25, 2010 at 12:38 am
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)
May 25, 2010 at 12:42 am
This was removed by the editor as SPAM
May 25, 2010 at 12:45 am
stewartc-708166 (5/25/2010)
TrickyRequires some knowledge of collations
Yes, the question was in Collations category 😛
May 25, 2010 at 12:54 am
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.
May 25, 2010 at 12:59 am
Out of curiousity. If one would like to get all strings containing a C from the below code... how would you do that in that collation?
create table #t (string varchar (5))
insert into #t (string) values ('ACH')
insert into #t (string) values ('ACY')
insert into #t (string) values ('AHHC')
select string from #t
where string like '%C%' -- will miss ACH
drop table #t
May 25, 2010 at 1:11 am
Great question, I'll keep that in mind as I also work with Czech_CI_AS collations.
tommyh (5/25/2010)
Out of curiousity. If one would like to get all strings containing a C from the below code... how would you do that in that collation?
create table #t (string varchar (5))
insert into #t (string) values ('ACH')
insert into #t (string) values ('ACY')
insert into #t (string) values ('AHHC')
select string from #t
where string like '%C%' -- will miss ACH
drop table #t
Suppose you have a Czech_CI_AS collation set up as your DB / table collation. Then you do this:
create table #t (string varchar (5))
insert into #t (string) values ('ACH')
insert into #t (string) values ('ACY')
insert into #t (string) values ('AHHC')
select string from #t
where string COLLATE Czech_BIN like '%C%' -- won't miss ACH
drop table #t
May 25, 2010 at 1:13 am
tommyh (5/25/2010)
Out of curiousity. If one would like to get all strings containing a C from the below code... how would you do that in that collation?
create table #t (string varchar (5))
insert into #t (string) values ('ACH')
insert into #t (string) values ('ACY')
insert into #t (string) values ('AHHC')
select string from #t
where string like '%C%' -- will miss ACH
drop table #t
It's very hard.
And if you are playing with our very special language, you can continue with case in case insensitive collation :w00t: : there is a difference if you are using 'ch', 'CH', 'Ch' and 'cH'.
May 25, 2010 at 1:14 am
Very nice question, I learned something new.
honza.mf
On SQL Server with CZECH_CI_AS collation returns 3 rows ('CH', 'Ch', and 'ch') - this collation is case insensitive.
Note that CZECH_CS_AS (case sensitive collation) returns the same 3 rows.
...congratulations on winning the world hockey championship 😉
May 25, 2010 at 1:30 am
ZeroFusion (5/25/2010)
Suppose you have a Czech_CI_AS collation set up as your DB / table collation. Then you do this:
create table #t (string varchar (5))
insert into #t (string) values ('ACH')
insert into #t (string) values ('ACY')
insert into #t (string) values ('AHHC')
select string from #t
where string COLLATE Czech_BIN like '%C%' -- will miss ACH
drop table #t
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.
May 25, 2010 at 1:41 am
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):
SET NOCOUNT ON;
DECLARE @name SYSNAME;
DECLARE cur CURSOR LOCAL STATIC FOR
SELECT name
FROM ::fn_helpcollations();
CREATE TABLE #result (collation SYSNAME, string VARCHAR(2));
OPEN cur;
FETCH NEXT FROM cur INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ALTER TABLE #t ALTER COLUMN string VARCHAR(2) COLLATE ' + @name);
IF @@ERROR = 0
INSERT #result (collation, string)
SELECT @name, string
FROM #t
WHERE string LIKE '_';
FETCH NEXT FROM cur INTO @name;
END;
CLOSE cur;
DEALLOCATE cur;
SELECT DISTINCT collation FROM #result;
SELECT DISTINCT collation, UPPER(string) FROM #result;
DROP TABLE #result;
It returns Croatian, Czech, Danish_Norwegian, Hungarian, Slovak, Traditional_Spanish, and Vietnamese on my machine.
Viewing 15 posts - 1 through 15 (of 55 total)
You must be logged in to reply to this topic. Login to reply