Blog RSS feed
Tom Muck's Blog: Title Case Function for SQL
News and Views
Title Case Function for SQL
Sunday, May 08, 2022 12:25:53 AM
I saw a few "title case" functions online for SQL Server, but none of them seem to take into account the fact that minor words like "the", "a", "and" are not capitalized in a title. Most of them also use slow-running loops. This function is fast, using a built in string_split function for splitting the title into a table of words, then using COALESCE to put it back together, using some string functions to capitalize major words:
CREATE FUNCTION fn_TitleCase (@InputString VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @val VARCHAR(4000)
Select @val = COALESCE(@val + ' ' +
CASE WHEN value IN ('and','as', 'but', 'for', 'if', 'nor', 'or', 'so', 'yet','a',
'an', 'the', 'as', 'at', 'by', 'for', 'in', 'of',
'off', 'on', 'per', 'to', 'up', 'via') THEN LOWER(value)
ELSE UPPER(LEFT(value,1)) + LOWER(RIGHT(value,len(value)-1) )
END,
UPPER(LEFT(value,1)) + LOWER(RIGHT(value,len(value)-1)) )
From STRING_SPLIT(@inputString, ' ');
RETURN ISNULL(@val,'')
END
Category tags: SQL
Posted by Tom Muck
Add comment |
View comments (1) |
Permalink
|
Trackbacks (0)
|
Digg This
Before posting comments or trackbacks, please read the posting policy.
