Sql: Capitalize First Letter Only

Managing Special Characters

In programming, as in life, hyphens and special characters can get tricky. For example, the string 'Smith-Jones' or the SQL developer's favorite restaurant 'McDonalds'. Here's how to handle them with style.

SELECT STRING_AGG(
        CASE 
            WHEN part = '' THEN '' 
            ELSE CONCAT(UPPER(LEFT(part, 1)), LOWER(SUBSTRING(part, 2))) 
        END, 
        '-' 
    ) AS new_column
FROM 
    (SELECT UNNEST(STRING_TO_ARRAY(column, '-')) AS part FROM table) AS sub;


The STRING_TO_ARRAY and UNNEST combo treats hyphen-separated parts independently making your string on-point.

Custom Function: The Shine to Your SQL

To perfectly manage multiple naming conventions and capitalize the first letter of each word, you can create a user-defined function pleasant to the eye:

CREATE FUNCTION CapitalizeFirstLetter(@InputString VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
    DECLARE @Index INT
    SET @Index = 1

    -- Putting a cap, one character at a time
    WHILE @Index <= LEN(@InputString)
    BEGIN
        IF @Index = 1 OR SUBSTRING(@InputString, @Index - 1, 1) = ' ' OR SUBSTRING(@InputString, @Index - 1, 1) = '-'
        BEGIN
            SET @InputString = STUFF(@InputString, @Index, 1, UPPER(SUBSTRING(@InputString, @Index, 1)))
        END
        SET @Index = @Index + 1
    END
	
    -- Making sure the rest of it knows who's boss
    SET @InputString = LOWER(@InputString)
    SET @Index = 1

    -- Handling the space and hyphen situations
    WHILE @Index <= LEN(@InputString)
    BEGIN
        IF @Index = 1 OR SUBSTRING(@InputString, @Index - 1, 1) IN (' ', '-')
        BEGIN
            SET @InputString = STUFF(@InputString, @Index, 1, UPPER(SUBSTRING(@InputString, @Index, 1)))
        END
        SET @Index = @Index + 1
    END
    RETURN @InputString
END


To use this snazzy custom function in your data:

UPDATE table SET column = dbo.CapitalizeFirstLetter(column);


Just make sure the custom function exists in your database schema prefixed with dbo or an appropriately permissioned schema.

More Power Tools: XML and Nodes

For complex string manipulations involving multiple words, using XML and nodes can greatly simplify your SQL journey:

SELECT (
    SELECT ' ' + UPPER(LEFT(T.value, 1)) + LOWER(SUBSTRING(T.value, 2, LEN(T.value)))
    FROM (
        SELECT CAST ('<M>' + REPLACE(REPLACE(column, '-', ' '), ' ', '</M><M>') + '</M>' AS XML) AS String
    ) AS A CROSS APPLY String.nodes ('/M') AS String(T)
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') as new_column
FROM table;


The XML and nodes approach gives you a lightsaber to slash through your strings with elegance and power.

Dynamic SQL for the Win

If you ever find yourself dealing with schemas or tables that might change, it's time for dynamic SQL to swing into action:

DECLARE @DynamicSQL NVARCHAR(MAX)
SET @DynamicSQL = 'SELECT CONCAT(UPPER(LEFT(' + @ColumnName + ', 1)), LOWER(SUBSTRING(' + @ColumnName + ', 2))) AS new_column FROM ' + @TableName
EXEC sp_executesql @DynamicSQL


Just remember to be a responsible SQL citizen and prevent SQL injections by sanitizing your dynamic elements or sourcing them securely.

Keep Errors at Bay

It's always a good idea to implement error checks to handle cases where the input isn't behaving. SQL can be overly caring sometimes, trying to uppercase numbers or special characters.

IF NOT EXISTS(SELECT * FROM table WHERE ISNUMERIC(column) = 1)
BEGIN
    -- If it is good, go for it.
END
ELSE
BEGIN
    RAISERROR('Column contains non-alphabetic characters.', 16, 1) -- Polite SQL disappointment expressed as an error
END


By applying this check, your SQL script safely avoids numeric values before capitalization, preventing unintended comb-overs on our data.

Dealing with the Big Data Beast

While these methods sit well for small to moderately-sized datasets, large tables can give you a scalability headache. Consider breaking down the process into smaller, chewable steps, or using SQL CLR functions if speed is of the essence.