Here's a solution from sqlmag.com by Itzik Ben-Gan that uses the title table from Pubs database as example.

UPDATE titles
  SET title =
      UPPER(LEFT(title, 1)) +
        LOWER(RIGHT(title, LEN(title) - 1))

WHILE @@rowcount > 0
  UPDATE titles
    SET title =
          STUFF(
            title,
            CHARINDEX(' ', title),
            2,
            '#' + UPPER(SUBSTRING(
                           title,
                           CHARINDEX(' ', title) + 1,
                           1)))
  WHERE CHARINDEX(' ', title) > 0

UPDATE titles
  SET title = REPLACE(title, '#', ' ')

It assumes that '#' character isn't present in the updated column.

>True. I forgot that proper() function works for multiple words in a string.
>
>>Sergey this will only work if the field has only one word for more words then only the first one will be caps.
>>
>>>>Hi,
>>>>
>>>>Does anyone know if there are a similar function to the proper() function in VFP on SQL Server 2000?
>>>>
>>>>I need to convert my data from uppercase ?
>>>>
>>>>
>>>
>>>No there isn't but you can build one

UPPER(LEFT(last_name,1)) + LOWER(SUBSTRING(last_name,2,1024))