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
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
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