PostgreSQL - Uppercase and Lowercase

Databases
1

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

2 

update [I]table_name[/I]
   set [I]field_name[/I] = upper(substr([I]field_name[/I], 1, 1))
                  + substr([I]field_name[/I], 2, length([I]field_name[/I]) - 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

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.


update registro1
  set pa_cognome = upper(substr(pa_cognome, 1, 1)) 
  + substr(pa_cognome, 2, length(pa_cognome) - 1)
4

Err, replace + with ||

5

Heyy thanks. I changed it a bit…


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

6 

update registro1
   set pa_nome = initcap(pa_nome)