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 of SQL code block or stored procedure.
ENDPlease Consider Subscribing
