Interview Question of the Week #008 – Write Scripts to Convert String to Title Case or Proper Case

A reader wrote to me just after an interview. The interviewer had asked for a script to convert a string to title case, then pointed to a website where the candidate could check the answer. The reader wanted to know: was that a fair interview question?

I would not reject the interview over that question alone. I would ask what title case means for the data, make an honest attempt, and explain where my solution stops working. If I did not know how to write it, I would say so. That conversation tells me more than whether someone memorized one string function.

What would I ask before writing SQL?

Do you want every word capitalized, or editorial title case with some small words left lowercase? Are these labels, headings, or people’s names? A rule that turns mcdonald into Mcdonald has formatted a string but has not recovered the person’s preferred spelling. Apostrophes, hyphens, repeated spaces, and mixed languages deserve explicit test cases.

I shared a title-case function and discussion years ago. Read the comments: readers found names and punctuation that the first example did not cover. The historical function also accepts varchar(4000) but uses a varchar(255) working variable, which can truncate a longer string.

A small interview demonstration

Use a disposable database created for this practice. CREATE OR ALTER replaces an existing function with the same name, so do not use a shared application database. This scratch-database example keeps the working value at the declared width. Its rule is intentionally narrow: lowercase the input, then uppercase the first character and characters following common separators. It is a formatting demonstration, not a universal name parser. Run it on a SQL Server version that supports CREATE OR ALTER FUNCTION.

GO
CREATE OR ALTER FUNCTION dbo.TitleCaseInterviewDemo
(@InputString nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
    IF @InputString IS NULL RETURN NULL;
    DECLARE @OutputString nvarchar(4000) = LOWER(@InputString);
    DECLARE @Index int = 1;
    DECLARE @Previous nchar(1);
    WHILE @Index <= LEN(@InputString)
    BEGIN
        SET @Previous = CASE WHEN @Index = 1 THEN N' '
                             ELSE SUBSTRING(@InputString, @Index - 1, 1) END;
        IF @Previous IN (N' ', N';', N':', N'!', N'?', N',', N'.',
                         N'_', N'-', N'/', N'&', N'''', N'(')
           AND NOT (@Previous = N''''
                    AND UPPER(SUBSTRING(@InputString, @Index, 1)) = N'S')
            SET @OutputString = STUFF(@OutputString, @Index, 1,
                UPPER(SUBSTRING(@InputString, @Index, 1)));
        SET @Index += 1;
    END;
    RETURN @OutputString;
END;
GO
SELECT dbo.TitleCaseInterviewDemo(N'this function converts a display label!') AS FormattedText;
SELECT dbo.TitleCaseInterviewDemo(N'owner''s desk') AS PossessiveExample;

I would test long input, NULL, repeated separators, and names supplied by the interviewer before promising more. The simple possessive s exception does not settle every apostrophe case.

Was the question fair? It can be, if the interviewer wants to hear how you define a problem and test your answer. If it seems unrelated to the job, I would ask about that after making an attempt. In an interview, I can show both my SQL and my judgment.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.