Post reply

lower case

  • December 17, 2013 at 12:23 pm

    #396002

    I need to do some analysis for data cleaning. I have a table called member which contains a column called lstname (last name), and most of the row in this column are all in caps, e.g. DAVIS, some of them with the first letter as caps and the rest of the letters are in lower case, e.g. Johnson. I need to write a script to pull all record with first letter in caps but the rest of the letter are all in lower case.

    Can you please help.

  • December 17, 2013 at 12:49 pm

    #1674214

    using collation is the key here; here's two adaptation from my snippets for you to play with:

    --find anything that has lower case in it at all.

    select * from member where lstname <> upper(lstname) collate Latin1_General_BIN

    --find anything that has upper case first letter and lower case in it at all.

    select * from member

    where lstname <> upper(lstname) collate Latin1_General_BIN

    AND LEFT(lstname) = LEFT(upper(lstname)) collate Latin1_General_BIN

    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!

  • December 17, 2013 at 12:49 pm

    #1674215

    I would suggest looking at the UPPER, LOWER, and SUBSTRING functions.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

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