# LIKE operator

• Susantha Bathige

Ten Centuries

Points: 1040

Susantha

• ChiragNS

One Orange Chip

Points: 26137

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

"Keep Trying"

• Jochen Vleming

Right there with Babe

Points: 729

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

But a good try anyways...

• A_A_R_T

SSC Eights!

Points: 890

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.powers

SSCrazy Eights

Points: 8700

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

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

• GSquared

SSC Guru

Points: 260824

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

"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: 53253

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

Good Question ....:)

• sudhanva

Ten Centuries

Points: 1343

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

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: 1004474

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

Points: 405

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

• kapil_kk

SSC-Insane

Points: 21316

good basic question of LIKE..

I like it 😛

_______________________________________________________________
http://www.sqlservercentral.com/articles/Best+Practices/61537/

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