<p>What’s the best way to capitalize the first letter of each word in a string in SQL Server.</p>

[{'answer_id': 55057, 'author': 'Espo', 'author_id': 2257, 'author_profile': 'https://Stackoverflow.com/users/2257', 'pm_score': 7, 'selected': True, 'text': '<p>From <a href="http://www.sql-server-helper.com/functions/initcap.aspx" rel="noreferrer">http://www.sql-server-helper.com/functions/initcap.aspx</a></p>\n\n<pre><code>CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) ) \nRETURNS VARCHAR(4000)\nAS\nBEGIN\n\nDECLARE @Index          INT\nDECLARE @Char           CHAR(1)\nDECLARE @PrevChar       CHAR(1)\nDECLARE @OutputString   VARCHAR(255)\n\nSET @OutputString = LOWER(@InputString)\nSET @Index = 1\n\nWHILE @Index &lt;= LEN(@InputString)\nBEGIN\n    SET @Char     = SUBSTRING(@InputString, @Index, 1)\n    SET @PrevChar = CASE WHEN @Index = 1 THEN \' \'\n                         ELSE SUBSTRING(@InputString, @Index - 1, 1)\n                    END\n\n    IF @PrevChar IN (\' \', \';\', \':\', \'!\', \'?\', \',\', \'.\', \'_\', \'-\', \'/\', \'&amp;\', \'\'\'\', \'(\')\n    BEGIN\n        IF @PrevChar != \'\'\'\' OR UPPER(@Char) != \'S\'\n            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))\n    END\n\n    SET @Index = @Index + 1\nEND\n\nRETURN @OutputString\n\nEND\nGO\n</code></pre>\n\n<p>There is a simpler/smaller one here (but doesn\'t work if any row doesn\'t have spaces, "Invalid length parameter passed to the RIGHT function."): </p>\n\n<p><a href="http://www.devx.com/tips/Tip/17608" rel="noreferrer">http://www.devx.com/tips/Tip/17608</a></p>\n'}, {'answer_id': 55153, 'author': 'Josef', 'author_id': 5581, 'author_profile': 'https://Stackoverflow.com/users/5581', 'pm_score': 2, 'selected': False, 'text': "<p>A variation of the one I've been using for quite some time is:</p>\n\n<pre><code>CREATE FUNCTION [widget].[properCase](@string varchar(8000)) RETURNS varchar(8000) AS\nBEGIN   \n    SET @string = LOWER(@string)\n    DECLARE @i INT\n    SET @i = ASCII('a')\n    WHILE @i &lt;= ASCII('z')\n    BEGIN\n        SET @string = REPLACE( @string, ' ' + CHAR(@i), ' ' + CHAR(@i-32))\n        SET @i = @i + 1\n    END\n    SET @string = CHAR(ASCII(LEFT(@string, 1))-32) + RIGHT(@string, LEN(@string)-1)\n    RETURN @string\nEND\n</code></pre>\n\n<p>You can easily modify to handle characters after items other than spaces if you wanted to.</p>\n"}, {'answer_id': 27742913, 'author': 'Andrey Morozov', 'author_id': 483408, 'author_profile': 'https://Stackoverflow.com/users/483408', 'pm_score': 2, 'selected': False, 'text': "<p>Another solution without using the loop - pure set-based approach with recursive CTE</p>\n\n<pre><code>create function [dbo].InitCap (@value varchar(max))\nreturns varchar(max) as\nbegin\n\n    declare\n        @separator char(1) = ' ',\n        @result varchar(max) = '';\n\n    with r as (\n        select value, cast(null as varchar(max)) [x], cast('' as varchar(max)) [char], 0 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j\n        union all\n        select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]\n        , left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]\n        , left(r.[value], 1)\n        , [no] + 1 [no]\n        from r where value &gt; '')\n\n    select @result = @result +\n    case\n        when ascii([char]) between 97 and 122 \n            then stuff(x, 1, 1, char(ascii([char])-32))\n        else x\n    end + @separator\n    from r where x is not null;\n\n    set @result = rtrim(@result);\n\n    return @result;\nend\n</code></pre>\n"}, {'answer_id': 31574024, 'author': 'Amrik', 'author_id': 1783751, 'author_profile': 'https://Stackoverflow.com/users/1783751', 'pm_score': 0, 'selected': False, 'text': '<p>Here is the simplest one-liner to do this:</p>\n<pre><code>SELECT LEFT(column, 1)+ lower(RIGHT(column, len(column)-1) ) FROM [tablename]\n</code></pre>\n'}, {'answer_id': 46344989, 'author': 'Kristofer', 'author_id': 1398417, 'author_profile': 'https://Stackoverflow.com/users/1398417', 'pm_score': 3, 'selected': False, 'text': "<p>As a table-valued function:</p>\n<pre><code>CREATE FUNCTION dbo.InitCap(@v AS VARCHAR(MAX))\nRETURNS TABLE\nAS\nRETURN \nWITH a AS (\n    SELECT (\n        SELECT UPPER(LEFT(value, 1)) + LOWER(SUBSTRING(value, 2, LEN(value))) AS 'data()'\n        FROM string_split(@v, ' ')\n        ORDER BY CHARINDEX(value,@v)\n        FOR XML PATH (''), TYPE) ret)\n\nSELECT CAST(a.ret AS varchar(MAX)) ret from a\nGO\n</code></pre>\n<p>Note that <code>string_split</code> requires <code>COMPATIBILITY_LEVEL</code> 130.</p>\n"}, {'answer_id': 47754555, 'author': 'Vignesh Sonaiya', 'author_id': 8294507, 'author_profile': 'https://Stackoverflow.com/users/8294507', 'pm_score': 1, 'selected': False, 'text': "<pre><code>BEGIN\nDECLARE @string varchar(100) = 'asdsadsd asdad asd'\nDECLARE @ResultString varchar(200) = ''\nDECLARE @index int = 1\nDECLARE @flag bit = 0\nDECLARE @temp varchar(2) = ''\nWHILE (@Index &lt;LEN(@string)+1)\n    BEGIN\n        SET @temp = SUBSTRING(@string, @Index-1, 1)\n        --select @temp\n        IF @temp = ' ' OR @index = 1\n            BEGIN\n                SET @ResultString = @ResultString + UPPER(SUBSTRING(@string, @Index, 1))\n            END\n        ELSE\n            BEGIN\n                \n                SET @ResultString = @ResultString + LOWER(SUBSTRING(@string, @Index, 1)) \n            END \n\n        SET @Index = @Index+ 1--increase the index\n    END\nSELECT @ResultString\nEND\n</code></pre>\n"}, {'answer_id': 52286176, 'author': 'Rene', 'author_id': 1739704, 'author_profile': 'https://Stackoverflow.com/users/1739704', 'pm_score': 0, 'selected': False, 'text': "<p>I was looking for the best way to capitalize and i recreate simple sql script</p>\n<p>how to use SELECT dbo.Capitalyze('this is a test   with multiple   spaces')</p>\n<p>result &quot;This Is A Test With Multiple Spaces&quot;</p>\n<pre><code>CREATE FUNCTION Capitalyze(@input varchar(100) )\n    returns varchar(100)\nas\nbegin\n    \n    declare @index int=0\n    declare @char as varchar(1)=' '\n    declare @prevCharIsSpace as bit=1\n    declare @Result as varchar(100)=''\n\n    set @input=UPPER(LEFT(@input,1))+LOWER(SUBSTRING(@input, 2, LEN(@input)))\n    set @index=PATINDEX('% _%',@input)\n    if @index=0\n        set @index=len(@input)\n    set @Result=substring(@input,0,@index+1)\n\n    WHILE (@index &lt; len(@input))\n    BEGIN\n        SET @index = @index + 1\n        SET @char=substring(@input,@index,1)\n        if (@prevCharIsSpace=1)\n        begin\n            set @char=UPPER(@char)\n            if (@char=' ')\n                set @char=''\n        end\n\n        if (@char=' ')\n            set @prevCharIsSpace=1\n        else\n            set @prevCharIsSpace=0\n\n        set @Result=@Result+@char\n        --print @Result\n    END\n    --print @Result\n    return @Result\nend\n</code></pre>\n"}, {'answer_id': 52621398, 'author': 'Akhil Singh', 'author_id': 7528842, 'author_profile': 'https://Stackoverflow.com/users/7528842', 'pm_score': 0, 'selected': False, 'text': '<p>fname is column name if fname value is akhil then UPPER(left(fname,1)) provide capital First letter(A) and substring function  SUBSTRING(fname,2,LEN(fname)) provide(khil) concate both using + then result is (Akhil) </p>\n\n<pre><code>select UPPER(left(fname,1))+SUBSTRING(fname,2,LEN(fname)) as fname\nFROM [dbo].[akhil]\n</code></pre>\n'}, {'answer_id': 53025811, 'author': 'Shashank Gupta', 'author_id': 10155755, 'author_profile': 'https://Stackoverflow.com/users/10155755', 'pm_score': 2, 'selected': False, 'text': "<p>If you are looking for the answer to the same question in Oracle/PLSQL then you may use the function INITCAP. Below is an example for the attribute <strong>dname</strong> from a table <strong>department</strong> which has the values ('sales', 'management', 'production', 'development').</p>\n\n<pre><code>SQL&gt; select INITCAP(dname) from department;\n\nINITCAP(DNAME)\n--------------------------------------------------\nSales\nManagement\nProduction\nDevelopment\n</code></pre>\n"}, {'answer_id': 56481595, 'author': 'Andrew Solomonik', 'author_id': 11610118, 'author_profile': 'https://Stackoverflow.com/users/11610118', 'pm_score': -1, 'selected': False, 'text': "<pre><code>IF OBJECT_ID ('dbo.fnCapitalizeFirstLetterAndChangeDelimiter') IS NOT NULL\n    DROP FUNCTION dbo.fnCapitalizeFirstLetterAndChangeDelimiter\nGO\n\nCREATE FUNCTION [dbo].[fnCapitalizeFirstLetterAndChangeDelimiter] (@string NVARCHAR(MAX), @delimiter NCHAR(1), @new_delimeter NCHAR(1))\nRETURNS NVARCHAR(MAX)\nAS \nBEGIN\n    DECLARE @result NVARCHAR(MAX)\n    SELECT @result = '';\n    IF (LEN(@string) &gt; 0)\n        DECLARE @curr INT\n        DECLARE @next INT\n        BEGIN\n            SELECT @curr = 1\n            SELECT @next = CHARINDEX(@delimiter, @string)\n            WHILE (LEN(@string) &gt; 0)\n                BEGIN\n                    SELECT @result = \n                        @result + \n                        CASE WHEN LEN(@result) &gt; 0 THEN @new_delimeter ELSE '' END +\n                        UPPER(SUBSTRING(@string, @curr, 1)) + \n                        CASE \n                            WHEN @next &lt;&gt; 0 \n                            THEN LOWER(SUBSTRING(@string, @curr+1, @next-2))\n                            ELSE LOWER(SUBSTRING(@string, @curr+1, LEN(@string)-@curr))\n                        END\n                    IF (@next &gt; 0)\n                        BEGIN\n                            SELECT @string = SUBSTRING(@string, @next+1, LEN(@string)-@next)\n                            SELECT @next = CHARINDEX(@delimiter, @string)\n                        END\n                    ELSE\n                        SELECT @string = ''\n                END\n        END\n    RETURN @result\nEND\nGO\n</code></pre>\n"}, {'answer_id': 58296137, 'author': 'Vitaly Borisov', 'author_id': 4119599, 'author_profile': 'https://Stackoverflow.com/users/4119599', 'pm_score': 0, 'selected': False, 'text': '<p>On <strong>SQL Server 2016+</strong> using JSON which gives guaranteed order of the words:</p>\n\n<pre><code>CREATE FUNCTION [dbo].[InitCap](@Text NVARCHAR(MAX))\nRETURNS NVARCHAR(MAX)\nAS\nBEGIN\n    RETURN STUFF((\n        SELECT \' \' + UPPER(LEFT(s.value,1)) + LOWER(SUBSTRING(s.value,2,LEN(s.value)))\n        FROM OPENJSON(\'["\' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Text,\'\\\',\'\\\\\'),\'"\',\'\\"\'),CHAR(9),\'\\t\'),CHAR(10),\'\\n\'),\' \',\'","\') + \'"]\') s\n        ORDER BY s.[key]\n    FOR XML PATH(\'\'),TYPE).value(\'(./text())[1]\',\'NVARCHAR(MAX)\'),1,1,\'\');\nEND\n</code></pre>\n'}, {'answer_id': 61159787, 'author': 'Glen', 'author_id': 1828277, 'author_profile': 'https://Stackoverflow.com/users/1828277', 'pm_score': 2, 'selected': False, 'text': "<pre><code>;WITH StudentList(Name) AS (\n      SELECT CONVERT(varchar(50), 'Carl-VAN')\nUNION SELECT 'Dean o''brian'\nUNION SELECT 'Andrew-le-Smith'\nUNION SELECT 'Eddy thompson'\nUNION SELECT 'BOBs-your-Uncle'\n), Student AS (\n    SELECT CONVERT(varchar(50), UPPER(LEFT(Name, 1)) + LOWER(SUBSTRING(Name, 2, LEN(Name)))) Name, \n           pos = PATINDEX('%[-'' ]%', Name)\n    FROM StudentList\n    UNION ALL\n    SELECT CONVERT(varchar(50), LEFT(Name, pos) + UPPER(SUBSTRING(Name, pos + 1, 1)) + SUBSTRING(Name, pos + 2, LEN(Name))) Name, \n           pos = CASE WHEN PATINDEX('%[-'' ]%', RIGHT(Name, LEN(Name) - pos)) = 0 THEN 0 ELSE pos + PATINDEX('%[-'' ]%', RIGHT(Name, LEN(Name) - pos)) END\n    FROM Student\n    WHERE pos &gt; 0\n)\nSELECT Name\nFROM Student \nWHERE pos = 0\nORDER BY Name \n</code></pre>\n\n<p>This will result in:</p>\n\n<ul>\n<li>Andrew-Le-Smith</li>\n<li>Bobs-Your-Uncle</li>\n<li>Carl-Van</li>\n<li>Dean O'Brian</li>\n<li>Eddy Thompson</li>\n</ul>\n\n<p>Using a recursive CTE set based query should out perform a procedural while loop query.\nHere I also have made my separate to be 3 different characters [-' ] instead of 1 for a more advanced example. Using PATINDEX as I have done allows me to look for many characters. You could also use CHARINDEX on a single character and this function excepts a third parameter StartFromPosition so I could further simply my 2nd part of the recursion of the pos formula to (assuming a space): pos = CHARINDEX(' ', Name, pos + 1).</p>\n"}, {'answer_id': 64925176, 'author': 'KodFun', 'author_id': 8783782, 'author_profile': 'https://Stackoverflow.com/users/8783782', 'pm_score': 0, 'selected': False, 'text': "<pre><code>GO\nCREATE FUNCTION [dbo].[Capitalize](@text NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS\nBEGIN\n    DECLARE @result NVARCHAR(MAX) = '';\n    DECLARE @c NVARCHAR(1);\n    DECLARE @i INT = 1;\n    DECLARE @isPrevSpace BIT = 1;\n\n    WHILE @i &lt;= LEN(@text)\n    BEGIN\n        SET @c = SUBSTRING(@text, @i, 1);\n        SET @result += IIF(@isPrevSpace = 1, UPPER(@c), LOWER(@c));\n        SET @isPrevSpace = IIF(@c LIKE '[    -]', 1, 0);\n        SET @i += 1;\n    END\n    RETURN @result;\nEND\nGO\n\nDECLARE @sentence NVARCHAR(100) = N'i-thINK-this    soLUTION-works-LiKe-a charm';\nPRINT dbo.Capitalize(@sentence);\n-- I-Think-This Solution-Works-Like-A Charm\n</code></pre>\n"}, {'answer_id': 67810946, 'author': 'Merin Nakarmi', 'author_id': 717955, 'author_profile': 'https://Stackoverflow.com/users/717955', 'pm_score': 1, 'selected': False, 'text': '<p>It can be as simple as this:</p>\n<pre><code>DECLARE @Name VARCHAR(500) = \'Roger\';\n\nSELECT @Name AS Name, UPPER(LEFT(@Name, 1)) + SUBSTRING(@Name, 2, LEN(@Name)) AS CapitalizedName;\n</code></pre>\n<p><a href="https://i.stack.imgur.com/dm87c.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dm87c.png" alt="enter image description here" /></a></p>\n'}, {'answer_id': 67998634, 'author': 'Ashraf Ali', 'author_id': 16241030, 'author_profile': 'https://Stackoverflow.com/users/16241030', 'pm_score': 1, 'selected': False, 'text': "<p>The suggested function works fine, however, if you do not want to create any function this is how I do it:</p>\n<pre><code>select ID,Name\n,string_agg(concat(upper(substring(value,1,1)),lower(substring(value,2,len(value)-1))),' ') as ModifiedName \nfrom Table_Customer \ncross apply String_Split(replace(trim(Name),'  ',' '),' ')\nwhere Name is not null\ngroup by ID,Name;\n</code></pre>\n<p>The above query split the words by space (' ') and create different rows of each having one substring, then convert the first letter of each substring to upper and keep remaining as lower. The final step is to string aggregate based on the key.</p>\n"}]
['https://Stackoverflow.com/questions/55054', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5170/']