LIKE operator

  • Susantha Bathige

    Ten Centuries

    Points: 1040

    Comments posted to this topic are about the item LIKE operator

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

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

    SSC-Addicted

    Points: 405

    Try this to get the records start with "Cu_"

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

  • kapil_kk

    SSC-Insane

    Points: 21316

    good basic question of LIKE..

    I like it 😛

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

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

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