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 Monday, May 24, 2010 10:19 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:43 AM
Points: 1,365, Visits: 1,313
Comments posted to this topic are about the item Simple LIKE with wildcard



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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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
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: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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
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: Friday, August 22, 2014 12:43 AM
Points: 1,365, Visits: 1,313
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
Posted Tuesday, May 25, 2010 12:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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
Posted Tuesday, May 25, 2010 12:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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
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: Today @ 5:18 AM
Points: 3,925, Visits: 5,112
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
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: Friday, August 22, 2014 12:43 AM
Points: 1,365, Visits: 1,313
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
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: Friday, August 22, 2014 12:43 AM
Points: 1,365, Visits: 1,313
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
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: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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 12345»»»

Permissions Expand / Collapse