Capitalize name with SQL function

Question

I wrote a function so that the first letter of the entered username is capitalized and all letters of the last name are capitalized.

The code I tried:

CREATE FUNCTION fn_capitalized(@name varchar(20), @surname varchar(20))
RETURNS VARCHAR(41)
AS
BEGIN
    DECLARE @result varchar(41)

    SET @result = UPPER(SUBSTRING(@name,1,1)) + LOWER(SUBSTRING(@name,2,LEN(@name))) + ' ' + UPPER(@surname)

    RETURN @result
END

But if user has 2 or more names how can I capitalize their other name

For example: select dbo.fn_xx('john jack','david')

I don't understand your example - you are passing 3 parameters to your function defined with 2 paramters; where do the parameter values come from? Presumably user input is originally a single string? Why not just take a single string as input? — Stu, Jul 05 '23 at 08:38
Use a CLR function and call `ToTitleCase` - some things SQL Server is not cut out for. — Dale K, Jul 05 '23 at 08:45
[SQL Server: Make all UPPER case to Proper Case/Title Case](https://stackoverflow.com/questions/230138/sql-server-make-all-upper-case-to-proper-case-title-case) Though so many of these use awful `WHILE` loops... — Thom A, Jul 05 '23 at 09:04

score 1 · Answer 1 · edited Jul 05 '23 at 08:46

If the user has more than two names and you want to capitalize the first letter of each name, you can modify the function accordingly.

CREATE FUNCTION fn_capitalized (@name varchar(100), @surname varchar(20))
RETURNS varchar(141)
AS
BEGIN
    DECLARE @result varchar(141);
    DECLARE @names TABLE (name varchar(20));
    
    -- Split the names into individual parts
    WHILE CHARINDEX(' ', @name) > 0
    BEGIN
        INSERT INTO @names (name) VALUES (LTRIM(RTRIM(SUBSTRING(@name, 1, CHARINDEX(' ', @name) - 1))));
        SET @name = LTRIM(RTRIM(SUBSTRING(@name, CHARINDEX(' ', @name) + 1, LEN(@name))));
    END;
    
    -- Handle the last name
    INSERT INTO @names (name) VALUES (LTRIM(RTRIM(@name)));
    
    -- Capitalize the first letter of each name
    SET @result = '';
    SELECT @result = @result + UPPER(SUBSTRING(name, 1, 1)) + LOWER(SUBSTRING(name, 2, LEN(name))) + ' '
    FROM @names;
    
    -- Add the capitalized surname
    SET @result = @result + UPPER(@surname);
    
    RETURN @result;
END;

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. **Would you kindly [edit] your answer to include additional details for the benefit of the community?** — Jeremy Caney, Jul 07 '23 at 00:25

score -2 · Answer 2 · edited Jul 05 '23 at 08:46

-2

SELECT INITCAP(name) AS capitalized_name
FROM your_table;

edited Jul 05 '23 at 08:46

Dale K

25,246
15
42
71

answered Jul 05 '23 at 08:36

Mani Kandan

1

1

`INITCAP` is not a SQL Server function. – Dale K Jul 05 '23 at 08:46
Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Jul 06 '23 at 01:14

Capitalize name with SQL function

2 Answers2