Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Honza Mensdorff » Simple LIKE with wildcard
Add to briefcase
56 posts, Page 1 of 6
12345»»»

Simple LIKE with wildcard Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
honza.mf
Posted Monday, May 24, 2010 10:19 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 1,103, Visits: 1,200
Comments posted to this topic are about the item Simple LIKE with wildcard


See, understand, learn, try, use efficient
© Dr.Plch
Post #927179
malleswarareddy_m
Posted Monday, May 24, 2010 10:55 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 1,852, Visits: 986
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)
Post #927187
tommyh
Posted Tuesday, May 25, 2010 12:01 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476, Visits: 1,943
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.
Post #927208
honza.mf
Posted Tuesday, May 25, 2010 12:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 1,103, Visits: 1,200
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.


See, understand, learn, try, use efficient
© Dr.Plch
Post #927212
Hugo Kornelis
Posted Tuesday, May 25, 2010 12:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:03 PM
Points: 5,244, Visits: 7,061
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!

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:03 PM
Points: 5,244, Visits: 7,061
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)

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #927221
Stewart "Arturius" Campbell
Posted Tuesday, May 25, 2010 12:42 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: Yesterday @ 8:31 AM
Points: 3,129, Visits: 4,312
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"

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #927222
honza.mf
Posted Tuesday, May 25, 2010 12:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 1,103, Visits: 1,200
stewartc-708166 (5/25/2010)
Tricky
Requires some knowledge of collations

Yes, the question was in Collations category


See, understand, learn, try, use efficient
© Dr.Plch
Post #927224
honza.mf
Posted Tuesday, May 25, 2010 12:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 1,103, Visits: 1,200
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.


See, understand, learn, try, use efficient
© Dr.Plch
Post #927226
tommyh
Posted Tuesday, May 25, 2010 12:59 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476, Visits: 1,943
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
Post #927228
« Prev Topic | Next Topic »

Add to briefcase
56 posts, Page 1 of 6
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.