Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Question Unanswered: Changing Character Case

    Does anyone have a solution to changing the case of characters in VARCHAR fields? I have several vendor names that have historically all been capitalized, and I would like to change them to Upper and Lower Case (as custom). For example:

    WIDGET, INC would become Widget, Inc
    ACME LTD would become Acme LTD
    JOHNSON & JOHNSON would become Johnson & Johnson

    I don' t mind if I have a stray character that inadvertantly gets changed to the wrong case... I can manage to the exceptions. I'm looking at roughly 11,000 records to change.

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Cool Use update DML command

    Hello,

    that depends on the database that you use:

    for Oracle and MySQL and MSSQL : UPDATE table SET company = UPPER(company);

    If you use another database, please let us know ...

    Hope that helps ?

    Best regards
    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Changing Character Case

    Originally posted by acg_ray
    Does anyone have a solution to changing the case of characters in VARCHAR fields? I have several vendor names that have historically all been capitalized, and I would like to change them to Upper and Lower Case (as custom). For example:

    WIDGET, INC would become Widget, Inc
    ACME LTD would become Acme LTD
    JOHNSON & JOHNSON would become Johnson & Johnson

    I don' t mind if I have a stray character that inadvertantly gets changed to the wrong case... I can manage to the exceptions. I'm looking at roughly 11,000 records to change.
    In Oracle, use the INITCAP function. However, this will convert "LTD" to "Ltd" in your example above.

  4. #4
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Re: Use update DML command

    UPPER would set all characters to Upper Case, would it not? (as LOWER would set all characters to Lower Case).

    I'm looking for the ability to set the first characters of strings to Upper, making rest lower, similar to the INITCAP function in Oracle.

    I'm guessing I could use a combination of UPPER, LOWER, and LEFT, which would get me to most of the changes, but that would leave me with lower case letters starting the second or third words in a name.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Use update DML command

    Originally posted by acg_ray
    UPPER would set all characters to Upper Case, would it not? (as LOWER would set all characters to Lower Case).

    I'm looking for the ability to set the first characters of strings to Upper, making rest lower, similar to the INITCAP function in Oracle.

    I'm guessing I could use a combination of UPPER, LOWER, and LEFT, which would get me to most of the changes, but that would leave me with lower case letters starting the second or third words in a name.
    I just took a look at some SQL Server documentation, and see that there seems to be no equivalent of INITCAP. I guess you could write your own function, using UPPER, LOWER, SUBSTRING and CHARINDEX.
    You would use CHARINDEX to find the spaces, then UPPER the character following each space.

  6. #6
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Re: Use update DML command

    Thanks. That's where I figured this was heading. That's a good direction, and I should be able to proceed from here. I appreciate the info.

  7. #7
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Re: Use update DML command

    I was able to get this to work (for the most part) with a combination of LEFT, RIGHT, SUBSTRING, LEN, CHARINDEX, UPPER, and LOWER commands. So it's not a bad tool for me to have on hand.

    I discovered as an afternote, that if I exported my results to Excel, I could use a command Proper(field) that would automatically do this for me. I could have then brought the results back into MSSQL. Really, a much easier process, since my data sets are small enough to be handled by Excel (15,000 rows).

Posting Permissions

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