the first letter to be uppercase and the other letters to be lowercase

Question

Hello, everyone.

I have multiple lines of strings separated by spaces.

For example:

'helLo evEryone'

'A niCE daY'

I need the first letter to be uppercase and the other letters to be lowercase.

Thank you a lot.

Answer 1

Hi @Potter123

You can try this.

create table test(InputString varchar(max));
insert into test values
('microsoft learning studio'),
('helLo evEryone'),
('A niCE daY'),
('TOmOrrOw will bE beTTeR');

CREATE OR ALTER FUNCTION [dbo].[InitCap] (@InputString varchar(max))
RETURNS VARCHAR(max)
AS
BEGIN
DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(max)
SET @OutputString = LOWER(@InputString)
SET @Index = 1
WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END
    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END
    SET @Index = @Index + 1
END
RETURN @OutputString
END
GO

select [dbo].[InitCap](InputString) as OutputString from test;

Output:

Best regards,

Percy Tang