LIKE operator

Question

Post reply

LIKE operator

Susantha Bathige

Ten Centuries

Points: 1040
More actions
August 12, 2008 at 11:28 pm

#189319

Comments posted to this topic are about the item LIKE operator
Susantha

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

for a second i thought :w00t:.... nice question

"Keep Trying"

Jochen Vleming Right there with Babe Points: 729 More actions · Answer 2

This one was quite simple (even for a n00b like myself :D)

But a good try anyways...

A_A_R_T SSC Eights! Points: 900 More actions · Answer 3

Nice one. To be honest: what I expected wasn't the right answer 😉 But after testing it I knew what was 🙂

Thanks for the lesson in wildcard characters!

Jim SSCrazy Eights Points: 8718 More actions · Answer 4

Tricky, tricky! Good question though. I fell into the trap probably because I haven't had enough coffee yet this morning. I saw the % signs and just completely forgot about the _ as a wildcard.

bhusank_neuronimbus SSC Journeyman Points: 75 More actions · Answer 5

It was good question. I create confusion for underscore. I just like logical

GSquared SSC Guru Points: 260824 More actions · Answer 6

You can also escape the wildcard characters with square-brackets.

where col like '[_]'

or

where col like '[%]'

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Good question, and a learning experience for me

I missed it too, not realizing that the underscore wouldn't catch 'C_us'. The lesson learned is that underscore as a wildcard means that there MUST be some character, but % will accept the absence of a character. Proved this out by modifying your question a touch.

create table #Like_Test( Col1 varchar(5) )

go

insert #Like_Test values('_Cu')

insert #Like_Test values('_Cus')

insert #Like_Test values('Cus_')

insert #Like_Test values('C_us')

insert #Like_Test values('Cus')

insert #Like_Test values('Cu_s')

select * from #Like_Test

where Col1 Like '%Cu_%'

select * from #Like_Test

where Col1 Like '%Cu%'

select * from #Like_Test

where Col1 Like '%C%u%'

drop table #like_test

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Anipaul SSC-Insane Points: 24681 More actions · Answer 8

Anipaul

SSC-Insane

Points: 24681

August 13, 2008 at 11:32 am

#857612

Good Question ....:)

sudhanva Ten Centuries Points: 1343 More actions · Answer 9

create table #Like_Test( Col1 varchar(5) )

insert #Like_Test values('_Cus')

insert #Like_Test values('Cus_')

insert #Like_Test values('C_us')

insert #Like_Test values('Cus')

insert #Like_Test values('Cu_s')

select * from #Like_Test where Col1 Like '%Cu_%'

How can we really get the row with value as "Cu_" then?

(I mean the last record in the above table)

Something like this doesnt works..

select * from #Like_Test where Col1 Like '%[Cu_]%'

arun.sas SSChampion Points: 11831 More actions · Answer 10

Adequate and nice to learn

To get the exact result

select * from #Like_Test

where Col1 Like '%Cu[_]%'

‘_’ is escape so

[_] is the valuable one

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Or

select * from #Like_Test

where Col1 Like '%Cu/_%' ESCAPE '/'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Deepak Jain-691547 SSC-Addicted Points: 465 More actions · Answer 12

Try this to get the records start with "Cu_"

select * from #Like_Test where Col1 Like 'Cu[_]%'

kapil_kk SSC-Insane Points: 21316 More actions · Answer 13

good basic question of LIKE..

I like it 😛

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/