Simple Function to capitalize a string

Please Consider Subscribing

Subscribe

Recently I was required to write a function that would capitalize the 1st letter in every word. This is a fairly common requirement and I felt that there would be some good syntax or code available on the Internet. On Googling I did find a couple of links but they all felt a bit cumbersome or outdated keeping in mind functionality that’s now available. In order to rectify this situation I felt that it would be better for me to write a new function which I could use subsequently for all queries. I was faced with the choice to use a scalar function or a table value function considering that in SQL 2019 we now have the ability to go ahead and inline scalar functions I felt that the choice between a scalar and table valued function wouldn’t really matter in terms of performance. And therefore I have written the below script which will considerably improve the performance of the query such that I wouldn’t necessarily have to worry about a row by row operation against every string. The below code is provided as is without any warranties having tested it for a number of common scenarios I feel that it works fine however feel free to make any changes as you need.

ALTER FUNCTION [dbo].[Capitalize] (@CapString VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
/***************************
Author :- Jayanth
Date :- 202105-11
Purpose - Capitilize any string 
***************************/
BEGIN
    -- Declare the return variable here
    DECLARE @result VARCHAR(1000)

    -- Add the T-SQL statements to compute the return value here
    SELECT @result = (
            SELECT STRING_AGG(value, ' ') AS Name
            FROM (
                SELECT UPPER(LEFT(value, 1)) + RIGHT(value,NULLIF(LEN(value),0)-1 ) AS Value
                FROM string_split(@Capstring, ' ')
                ) a
            )

    -- Return the result of the function
    RETURN @result
END