Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    18

    Question Unanswered: Sorting records in lower case

    Hello,

    I am working on a query, It finds records which are in lower case from table.

    eg.

    richard
    john
    andy

    The below query which I have designed, shows the required output...
    but fields containing numeric & null values are also displayed, which i don't require.

    --
    SELECT * FROM `table`
    WHERE (NAME COLLATE latin1_bin ) = lower(NAME);
    --

    Kindly Advice
    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so exclude those rows by using an appropriate WHERE clause
    eg AND NOT IS NULL(name)
    and/or modify MIKE-BIKE-KITE's regexp to exclude numbers, although to be fair regexp is not for the faint hearted
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Posts
    18
    Quote Originally Posted by healdem View Post
    so exclude those rows by using an appropriate WHERE clause
    eg AND NOT IS NULL(name)
    and/or modify MIKE-BIKE-KITE's regexp to exclude numbers, although to be fair regexp is not for the faint hearted
    Hey Healdem,

    Designed this query for lower case...and got fully working !!

    SELECT * FROM `table`
    WHERE NAME regexp BINARY'^[a-z]'


    Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andy982183 View Post
    Designed this query for lower case...and got fully working !!Thanks
    your testing was not very rigourous, was it

    try this --
    Code:
    CREATE TABLE test_mixedcase
    ( name VARCHAR(99)
    );
    
    INSERT INTO test_mixedcase (name) VALUES 
     ( 'this is all lower case' )
    ,( 'this starts in lower case AND THEN GOES UPPER' )
    ;
    SELECT * FROM test_mixedcase
    WHERE name REGEXP BINARY '^[a-z]'
    tell me which rows it returned

    hint: bofadem

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM `table`
       WHERE name NOT LIKE '%[^a-z]%'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •