Post reply

islowercase

  • July 2, 2009 at 5:55 am

    #132377

    hi,

    I am trying to only display results when the surname is all lowercase.

    I have tried select * from customer where surname= lower(surname)

    but it doesn't work.

    can this be done ?

  • July 2, 2009 at 6:08 am

    #1019068

    try this:

    select *

    from customer

    where surname = LOWER(surname) COLLATE SQL_Latin1_General_CP1_CS_AS

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 2, 2009 at 6:11 am

    #1019069

    Try this

    select * from customer where upper(surname) surname

    blog: http://sarveshsingh.comTwitter: @sarveshsing

  • July 2, 2009 at 6:16 am

    #1019072

    superb.

    thank you, why does that work then ?

  • July 2, 2009 at 6:20 am

    #1019074

    Because you using a case sensitive coalation so the query checks for case sensitivity

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 2, 2009 at 6:28 am

    #1019078

    sarvesh singh (7/2/2009)

    Try this

    select * from customer where upper(surname) surname

    that won't work the way you think, unless the database you are using is using case sensitive collation...you've got to go with what Christopher Stobbs suggested.

    for reference, even binary_checksum will not give you a case sensitive comparison; i tried it against an address column and differences between lower and regular still produce many identical checksums:

    select * from

    (SELECT '1201 Garrard St' as surname) customer

    where upper(surname) surname --they are -not- different

    select * from (

    select binary_checksum('1201 Garrard St') as chk1,binary_checksum(lower('1201 Garrard St'))as chk2,'1201 Garrard St' AS addr,lower('1201 Garrard St')as laddr ) X

    where chk1=chk2

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

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