Ygthkb

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

Another option is to handle this via SQLCLR. There is even a method already available in .NET that does this: TextInfo.ToTitleCase (in System.Globalization). This method will Upper-Case the first letter of each word, and Lower-Case the remaining letters. Unlike the other proposals here, it also skips words that are in all upper-case, assuming them to be acronyms. Of course, if this behavior is desired, it would be easy enough to update any of the T-SQL suggestions to do this as well.

One benefit of the .NET method is that it can Upper-Case letters that are Supplementary Characters. For example: DESERET SMALL LETTER OW has an upper-case mapping of DESERET CAPITAL LETTER OW ^{(both show up as boxes when I paste them into here)}, but the UPPER() function does not change the lower-case version to upper-case, even when the default Collation for the current Database is set to Latin1_General_100_CI_AS_SC. This seems consistent with the MSDN documentation which does not list UPPER and LOWER in the chart of functions that behave differently when using an _SC Collation: Collation and Unicode Support: Supplementary Characters.

SELECT N'DESERET SMALL LETTER OW' AS [Label], NCHAR(0xD801)+NCHAR(0xDC35) AS [Thing]

UNION ALL

SELECT N'DESERET CAPITAL LETTER OW' AS [Label], NCHAR(0xD801)+NCHAR(0xDC0D) AS [Thing]

UNION ALL

SELECT N'SmallButShouldBeCapital' AS [Label], UPPER(NCHAR(0xD801)+NCHAR(0xDC35)) AS [Thing]

Returns (enlarged so you can actually see the Supplementary Character):

You can see the full (and current) list of characters that are lower-case and change to upper-case using the following search feature at Unicode.org (you can see the Supplementary Characters by scrolling down until you get to the "DESERET" section, or just hit Control-F and search for that word):

http://unicode.org/cldr/utility/list-unicodeset.jsp?a=%5B%3AChanges_When_Titlecased%3DYes%3A%5D

Though to be honest, this isn't a huge benefit since it is doubtful that anyone is actually using any of the Supplementary Characters that can be title-cased. Either way, here is the SQLCLR code:

using System.Data.SqlTypes;

using System.Globalization;

using Microsoft.SqlServer.Server;



public class TitleCasing

{

    [return: SqlFacet(MaxSize = 4000)]

    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]

    public static SqlString TitleCase([SqlFacet(MaxSize = 4000)] SqlString InputString)

    {

        TextInfo _TxtInf = new CultureInfo(InputString.LCID).TextInfo;

        return new SqlString (_TxtInf.ToTitleCase(InputString.Value));

    }

}

Here is @MikaelEriksson's suggestion -- modified slightly to handle NVARCHAR data as well as skip words that are all upper-case (to more closely match the behavior of the .NET method) -- along with a test of that T-SQL implementation and of the SQLCLR implementation:

SET NOCOUNT ON;

DECLARE @a NVARCHAR(50);



SET @a = N'qWeRtY kEyBoArD TEST<>&''"X one&TWO '

         + NCHAR(0xD801)+NCHAR(0xDC28)

         + N'pPLe '

         + NCHAR(0x24D0) -- ⓐ  Circled "a"

         + NCHAR(0xFF24) -- Ｄ  Full-width "D"

         + N'D u'

         + NCHAR(0x0308) -- ̈  (combining diaeresis / umlaut)

         + N'vU'

         + NCHAR(0x0308) -- ̈  (combining diaeresis / umlaut)

         + N'lA';

SELECT @a AS [Original];



SELECT STUFF((

       SELECT N' '

              + IIF(UPPER(T3.V) <> T3.V COLLATE Latin1_General_100_BIN2, 

                    UPPER(LEFT(T3.V COLLATE Latin1_General_100_CI_AS_SC, 1))

                    + LOWER(STUFF(T3.V COLLATE Latin1_General_100_CI_AS_SC, 1, 1, N'')),

                    T3.V)

       FROM (SELECT CAST(REPLACE((SELECT @a AS N'*' FOR XML PATH('')), N' ', N'<X/>')

                    AS XML).query('.')) AS T1(X)

       CROSS APPLY T1.X.nodes('text()') AS T2(X)

       CROSS APPLY (SELECT T2.X.value('.', 'NVARCHAR(70)')) AS T3(V)

       FOR XML PATH(''), TYPE

       ).value('text()[1]', 'NVARCHAR(70)') COLLATE Latin1_General_100_CI_AS_SC, 1, 1, N'')

                AS [Capitalize first letter only];



SELECT dbo.TitleCase(@a) AS [ToTitleCase];

Another difference in behavior is that this particular T-SQL implementation splits on only spaces, whereas the ToTitleCase() method considers most non-letters to be word separators (hence the difference in handling of the "one&TWO" part).

Both implementations handle combining sequences correctly. Each of the accented letters in "üvÜlA" are comprised of a base letter and a combining diaeresis / umlaut (the two dots above each letter), and they are correctly converted to the other case in both tests.

Finally, one unexpected disadvantage to the SQLCLR version is that in coming up with various tests, I found a bug in the .NET code related to its handling of the Circled Letters (which has now been reported on Microsoft Connect — UPDATE: Connect has been moved to /dev/null -- literally -- so I might need to resubmit this if the problem still exists). The .NET library treats the Circled Letters as word separators, which is why it does not turn the "ⓐＤD" into "Ⓐｄd" as it should.

FYI

A pre-done SQLCLR function encapsulating the TextInfo.ToTitleCase method mentioned above is now available in the Free version of SQL# (which I wrote) as String_ToTitleCase and String_ToTitleCase4k.

😺

As an alternative to Mikael Eriksson's answer, you could consider using the proprietary T-SQL handling of variable setting in multi-row select statements.

In SQL Server, when a variable is being set as part of a SELECT statement, each row will execute an iteration of the set logic.

Folks often use this method for concatenating strings, though it's unsupported and there are some officially documented issues with it. The official problem relates to particular ORDER BY characteristics, and we don't need that here, so perhaps it's a safe option.

Here, we iterate over the 26 letters of the alphabet and replace them with an upper case version if they are preceded by a space. (We prep the string initially by capitalizing the first letter and making the rest lower case, as you did in your question.)

The SQL is a little complex because it requires the use of a Tally Table-- a table of numbers-- to generate the 26 iterations of replacing that it's doing. You can make a handy inline table-valued user defined function (TVF) to produce that table of numbers or you could even use a physical table.

A drawback of this option is that it can't be part of an inline TVF as it needs to involve setting a variable. So if you wanted to apply this method to a column of your output, you would need to wrap it into a multi-statement TVF or a scalar user defined function.

However, its query plan is much simpler and it is probably significantly faster than the XML method. You could argue it's easier to understand, too (especially if you have your own tally table).

DECLARE

    @a VARCHAR(15) = 'qWeRtY kEyBoArD';



SELECT

    @a = UPPER(LEFT(@a,1)) + LOWER(SUBSTRING(@a,2,LEN(@a)));



WITH TallyTableBase AS

(

    SELECT

        0 AS n

    FROM    (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS t(n)

)

SELECT

    @a = REPLACE(@a, ' ' + CHAR(n.n), ' ' + CHAR(n.n))

FROM        (

                SELECT      TOP 26 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) + 64 AS n

                FROM        TallyTableBase a

                CROSS JOIN  TallyTableBase b

            ) AS n;



SELECT

    @a AS [NewValue];

(I tested this using a much larger string and it was about 6ms vs 14ms for the XML solution.)

There are a number of additional limitations with this solution. As written, it assumes a case insensitive collation, though you could eliminate that issue by specifying a collation or running LCASE on the search term, at the cost of some performance. It also only addresses standard ASCII letters and relies on their placement in the character set, so it would do nothing with ñ.

Assuming you are only looking to capitalize words following a space, here is an another way you could do it.

DECLARE @String VARCHAR(1000)

SET @String = 'qWeRtY kEyBoArD tEst'



/*

Set the string to all lower case and

add a space at the beginning to ensure

the first letter gets capitalized

in the CTE

*/

SET @String = LOWER(' ' + @String)  



/*

Use a Tally "Table" as a means of

replacing the letter after the space

with the capitalize version of the

letter

*/

;WITH TallyTable

AS

(

    SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as N

    FROM master.sys.all_columns a CROSS JOIN master.sys.all_columns b



)

SELECT @String = REPLACE(@String,SUBSTRING(@String,CHARINDEX(' ',@String,N), 2),UPPER(SUBSTRING(@String,CHARINDEX(' ',@String,N), 2)))

FROM TallyTable

WHERE CHARINDEX(' ',@String,N) <> 0



--Remove the space added to the beginning of the string earlier

SET @String = RIGHT(@String,LEN(@String) - 1)

Might not be bullet-proof but I hope it's a helpful contribution to this thread.

DECLARE @t VARCHAR(50) = 'the quick brown fox jumps over the lazy dog', @i INT = 0



DECLARE @chk VARCHAR(1)



WHILE @i <= LEN(@t)

BEGIN

    SELECT @chk=SUBSTRING(@t,@i,1)

        IF @chk = CHAR(32)

        BEGIN

            SET @t = STUFF(@t,@i+1,1,UPPER(SUBSTRING(@t,@i+1,1)))

        END

    SET @i=@i+1

END

PRINT @t

Below is the procedure I used in a Firebird database to do this.
Probably can be cleaned up a lot but it got the job done for me.

set term ~;



Create Procedure EachWordCap



As



Declare Variable lcaption varchar(33);

Declare Variable lcurrentpos integer;

Declare Variable lstringlen integer;

begin

    for select ' ' || trim(lower(imagedata.imagename)) from imagedata

    where imagedata.imagename is not null and imagedata.imagename != ''

    into :lcaption

    do 

    begin

        lcurrentpos = 0;

        lstringlen = char_length(lcaption);

        while (lcurrentpos != 1) do

        begin

            lcurrentpos = position(' ', lcaption, iif(lcurrentpos = 0, 1,lcurrentpos)) + 1 ;

            lcaption = left(lcaption,lcurrentpos - 1) || upper(substring(lcaption from lcurrentpos for 1)) || right(lcaption,lstringlen - lcurrentpos);

        end

        --Put what you want to do with the text in here

    end

end~

set term ;~

Recursive CTEs are quite good for this sort of thing.

Probably not particularly efficient for large operations, but does allow for this kind of operation in a pure SQL select statement:

declare @a varchar(100) 



set @a = 'tHe qUiCk bRoWn FOX jumps   OvEr The lAZy dOG';



WITH [CTE] AS (

  SELECT CAST(upper(Left(@a,1)) + lower(substring(@a,2,len(@a))) AS VARCHAR(100)) AS TEXT,

         CHARINDEX(' ',@a) AS NEXT_SPACE

  UNION ALL

  SELECT CAST(Left(TEXT,NEXT_SPACE) + upper(SubString(TEXT,NEXT_SPACE+1,1)) + SubString(TEXT,NEXT_SPACE+2,1000) AS VARCHAR(100)),

         CHARINDEX(' ',TEXT, NEXT_SPACE+1)

  FROM [CTE]

  WHERE NEXT_SPACE <> 0

)



SELECT TEXT

FROM [CTE]

WHERE NEXT_SPACE = 0

Output:

The Quick Brown Fox Jumps   Over The Lazy Dog

I like this version. It is simple, and can be used to create a function, you just have to have the right version of SQL Server:

WITH words

AS (

    SELECT upper(left(Value, 1)) + lower(substring(Value, 2, len(Value))) AS word

    FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ')

    )

SELECT STRING_AGG(words.word, ' ')

FROM words

Test Data

declare @word varchar(100)

with good as (select 'good' as a union select 'nice' union select 'fine')

select @word = (SELECT TOP 1 a FROM good ORDER BY NEWID())

Implementation

select substring(Upper(@word),1,1) + substring(@word, 2, LEN(@word))