Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Simple LIKE with wildcard Expand / Collapse
Author
Message
Posted Tuesday, May 25, 2010 1:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:01 AM
Points: 300, Visits: 153
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

Post #927234
Posted Tuesday, May 25, 2010 1:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:23 AM
Points: 1,409, Visits: 1,316
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 : there is a difference if you are using 'ch', 'CH', 'Ch' and 'cH'.




See, understand, learn, try, use efficient
© Dr.Plch
Post #927235
Posted Tuesday, May 25, 2010 1:14 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Post #927237
Posted Tuesday, May 25, 2010 1:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:23 AM
Points: 1,409, Visits: 1,316
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.




See, understand, learn, try, use efficient
© Dr.Plch
Post #927243
Posted Tuesday, May 25, 2010 1:41 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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.
Post #927249
Posted Tuesday, May 25, 2010 1:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:01 AM
Points: 300, Visits: 153
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".
Post #927252
Posted Tuesday, May 25, 2010 1:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:09 PM
Points: 1,940, Visits: 1,173
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)
Post #927258
Posted Tuesday, May 25, 2010 1:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:09 PM
Points: 1,940, Visits: 1,173
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)
Post #927262
Posted Tuesday, May 25, 2010 2:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 6,098, Visits: 8,367
honza.mf (5/25/2010)
stewartc-708166 (5/25/2010)
Tricky
Requires some knowledge of collations

Yes, the question was in Collations category

I had not even seen that!
(note to self: in future, check question category before answering!)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #927269
Posted Tuesday, May 25, 2010 2:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:23 AM
Points: 1,409, Visits: 1,316
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
Post #927271
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse