LIKE operator

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

    Susantha

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

    "Keep Trying"

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

    But a good try anyways...

  • 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!

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

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

  • 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

  • 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

  • Good Question ....:)

  • 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_]%'

  • 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

  • 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
  • Try this to get the records start with "Cu_"

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

  • 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