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

Potter123 260 Reputation points
2023-10-31T09:26:28.97+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,652 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,464 questions
0 comments No comments
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,041 Reputation points Microsoft Vendor
    2023-10-31T09:42:40.4133333+00:00

    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:

    User's image

    Best regards,

    Percy Tang

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful