2

I have tried various Title Case solutions from here and they give me an 0 in the column. I have to change the LEN to LENGTH to get it to run. If I strip the statement down to its 2 parts it works.

 UPDATE names 
    SET
    surname = UPPER(LEFT(surname, 1)) +
    LOWER(RIGHT(surname, LENGTH(surname) - 1));

Or

update  names
set     surname = upper(substring(surname,1,1)) + 
        lower(substring(surname, 2, length(surname)-1))
where   length(surname) > 0 ;
Joe Taras
  • 15,166
  • 7
  • 42
  • 55
Peter Brooks
  • 49
  • 10

1 Answers1

2

If you use MySql you must use function CONCAT, to concatenate two strings.

In this way:

update  names
set     surname = concat(upper(substring(surname,1,1)),
        lower(substring(surname, 2, length(surname)-1)))
where   length(surname) > 0 ;

It's good use WHERE condition so you prevent possible nullable field

N.B. Your result is 0 because DBMS tries to sum two number (implicit conversion by your string, so you'll get 0 instead your result).

Show Fiddle

Joe Taras
  • 15,166
  • 7
  • 42
  • 55
  • 1
    @PeterBrooks if this did helped you kindly repay the effort given by the answerer thru an upvote or accept – Kevin Jul 24 '14 at 10:37
  • I've just spent a bit of searching for how to do what in the US we call "Title Case", and I keep finding this "Proper Case" solution on various pages. This solution is shorter that the Proper Case code over here http://www.artfulsoftware.com/infotree/qrytip.php?id=122, but it still seems a bit of a pain that Oracle would make us have to go through all that, and not have a built-in function to just do this in a black-box way. Just sayin. – Eric Hepperle - CodeSlayer2010 Sep 10 '15 at 05:37