Tom Muck

Alpha Dog Blues Band
Home page
All articles
All Extensions | Extension News | Extension FAQs | Customer Login
Books authored or co-authored by Tom Muck
Extensions, books, and other products | Customer Login
Your current cart contents
Tom-Muck.com Blog | CMXTraneous Blog | Flash Remoting Blog
About the site

Blog

Tom Muck's BlogTom Muck's Blog

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

Before posting comments or trackbacks, please read the posting policy.

Full Blog Calendar

Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
About | Privacy Policy | Contact | License Agreement | ©2002-2022 Tom Muck | Dreamweaver Extensions