Hello ,
If you want to get each word with capital number then go for Sir Peter Leow's answer
or else
If you want to make only first letter to be capital and others remaining same then try
SELECT UPPER(LEFT(YourColumnName,1))+SUBSTRING(YourColumnName,2,LEN(YourColumnName)))FROM [YourTableName]
Thanks