Capitalize only the first letter of each word of each sentence in SQL Server

declare @a varchar(30); 

set @a = 'qWeRtY kEyBoArD TEST<>&''"X';

select stuff((
       select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
       from (select cast(replace((select @a as '*' for xml path('')), ' ', '<X/>') as xml).query('.')) as T1(X)
         cross apply T1.X.nodes('text()') as T2(X)
         cross apply (select T2.X.value('.', 'varchar(30)')) as T3(V)
       for xml path(''), type
       ).value('text()[1]', 'varchar(30)'), 1, 1, '') as [Capitalize first letter only];

This first converts the string to XML by replacing all spaces with the empty tag <X/>. Then it shreds the XML to get one word per row using nodes(). To get the rows back to one value it uses the for xml path trick.

In SQL Server 2016 you can do this with R, eg

-- R capitalisation code stolen from here:
-- http://stackoverflow.com/questions/6364783/capitalize-the-first-letter-of-both-words-in-a-two-word-string

EXEC sp_execute_external_script
    @language = N'R',
    @script = N'
simpleCap <- function(x) {
  s <- strsplit(x, " ")[[1]]
  paste(toupper(substring(s, 1,1)), substring(s, 2),
        sep="", collapse=" ")
}             

OutputDataSet <- as.data.frame((sapply(as.vector(InputDataSet$xtext), simpleCap)))',
    @input_data_1 = N'SELECT LOWER(testString) xtext FROM dbo.testStrings'
WITH RESULT SETS ( ( properCase VARCHAR(50) NOT NULL ) );

Whether you should or not is a different question : )

Maybe I'm being silly but checking the below query I've written against some of the provided, this seems to be a bit more efficient (depending on indexing).

The code is a bit stupid, but isn't there a saying that if it looks stupid but it works then it's not stupid.

Begin

    Declare @text Varchar(30);

    Set @text = 'qWeRtY kEyBoArD TEST<>&''"X';

    Declare @1 Varchar(2)= ' a'
      , @2 Varchar(2)= ' b'
      , @3 Varchar(2)= ' c'
      , @4 Varchar(2)= ' d'
      , @5 Varchar(2)= ' e'
      , @6 Varchar(2)= ' f'
      , @7 Varchar(2)= ' g'
      , @8 Varchar(2)= ' h'
      , @9 Varchar(2)= ' i'
      , @10 Varchar(2)= ' j'
      , @11 Varchar(2)= ' k'
      , @12 Varchar(2)= ' l'
      , @13 Varchar(2)= ' m'
      , @14 Varchar(2)= ' n'
      , @15 Varchar(2)= ' o'
      , @16 Varchar(2)= ' p'
      , @17 Varchar(2)= ' q'
      , @18 Varchar(2)= ' r'
      , @19 Varchar(2)= ' s'
      , @20 Varchar(2)= ' t'
      , @21 Varchar(2)= ' u'
      , @22 Varchar(2)= ' v'
      , @23 Varchar(2)= ' w'
      , @24 Varchar(2)= ' x'
      , @25 Varchar(2)= ' y'
      , @26 Varchar(2)= ' z';

Set @text=' '+@text

    Select  LTrim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Lower(@text) ,
                                                              @1 , Upper(@1)) ,
                                                              @2 , Upper(@2)) ,
                                                              @3 , Upper(@3)) ,
                                                              @4 , Upper(@4)) ,
                                                              @5 , Upper(@5)) ,
                                                              @6 , Upper(@6)) ,
                                                              @7 , Upper(@7)) ,
                                                              @8 , Upper(@8)) ,
                                                              @9 , Upper(@9)) ,
                                                              @10 , Upper(@10)) ,
                                                              @11 , Upper(@11)) ,
                                                              @12 , Upper(@12)) ,
                                                              @13 , Upper(@13)) ,
                                                              @14 , Upper(@14)) ,
                                                              @15 , Upper(@15)) ,
                                                              @16 , Upper(@16)) ,
                                                              @17 , Upper(@17)) ,
                                                              @18 , Upper(@18)) ,
                                                              @19 , Upper(@19)) ,
                                                              @20 , Upper(@20)) ,
                                                            @21 , Upper(@21)) ,
                                                    @22 , Upper(@22)) , @23 ,
                                            Upper(@23)) , @24 , Upper(@24)) ,
                            @25 , Upper(@25)) , @26 , Upper(@26)));


end