SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Milan, Italy
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PostgreSQL - Uppercase and Lowercase

    Hello everyone

    I have a table with lots of records all in UPPERCASE. But i need to change them to the normal writing style. Like only the first letter in Uppercase the rest in Lowercase.
    I really don't want to insert them all over.

    Is there any automatic way to do this??

    Thanks
    dReAm

  2. #2
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    update table_name
       set field_name = upper(substr(field_name, 1, 1))
                      + substr(field_name, 2, length(field_name) - 1)
    Make sure to back up the table, or at least perform a select query with the expression above to not break your data.

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Milan, Italy
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks

    i get this error

    ERROR: operator does not exist: text + text
    HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.

    Code:
    update registro1
      set pa_cognome = upper(substr(pa_cognome, 1, 1)) 
      + substr(pa_cognome, 2, length(pa_cognome) - 1)
    dReAm

  4. #4
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Err, replace + with ||

  5. #5
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Milan, Italy
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heyy thanks. I changed it a bit..

    Code:
    update registro1 set pa_nome = (upper(substr(pa_nome, 1, 1)) || lower(substr(pa_nome, 2, length(pa_nome) - 1)))
    Only thing is when i have "RONAN KEATING" in my field it changes to "Ronan keating" which is ok.
    But what if i want "Ronan Keating"

    O____________o how do i do that??

    Thanks
    dReAm

  6. #6
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    update registro1
       set pa_nome = initcap(pa_nome)


Bookmarks

Posting Permissions

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