Post reply

Lower to Upper Case

  • December 7, 2002 at 6:40 pm

    #79957

    Hello All

    I am looking for a way to change Only the First character of a text string in a field to UpperCase. For example... If I have a field with a FirstName and it is currently stored as All LowerCase, When I query out the data, I want to show only the very First Character as an UpperCase character. This is know as Proper Case in apps like Excel.

    Table Query Result

    andrew -> Andrew

    Thanks

    Andrew

    How long a minute is....

    Depends on what side of the bathroom door you are on.


    How long a minute is....
    Depends on what side of the bathroom door you are on.

  • December 7, 2002 at 9:15 pm

    #443559

    This should do it.

    SELECT UCASE(LEFT(col,1)) + LCASE(SUBSTRING(col,2,LEN(COL) - 1))

  • December 8, 2002 at 7:19 am

    #443579

    Antares solution will work. however, this function is better served by your client app that inserts the data. names are notoriously inconsistent in particular when you start with non-US names.

    van der Weeg

    de la Hoya

    Van der weeg

    De La Hoya

    We tend to use what ever the user entered rather than try to guess how to actually capitalize it.

    if you want to incorporate this into your data base logic, i would create a Trigger that updates it on storing rather than stringing a column every time it's selected.

  • December 8, 2002 at 7:39 am

    #443586

    I agree with the trigger except keep in mind it will increase transactions. You would be better to wrap query in an SP and have it done there to eliminate the second transation. Or try to get put into client app design (I prefer SP personally).

    Also, forgot. If you are running SQL 2000 keep in mind that you can create a UDF (User defined function) to wrap the syntax I gave you so you can reuse it and decrease the overall typing you have to do.

  • December 8, 2002 at 7:55 am

    #443589

    Thank You for your reply. I am wanting to use this on a SQL 2000 DB, with an ASP front end. I greatly appreciate your suggestions and advice.

    I am just not sure what you mean when in your string is "col". Is that column_name?

    Again

    Thank You

    Andrew

    How long a minute is....

    Depends on what side of the bathroom door you are on.


    How long a minute is....
    Depends on what side of the bathroom door you are on.

  • December 8, 2002 at 10:11 am

    #443590

    Yes, I meant column_name. I am just used to using col when referencing with developers here. Sorry.

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

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