Caps in each word of 1st letter

Question

Hi Team,

How to update 1st letter as caps of each word in sql?

exp:

input: microsoft learning studio

output: Microsoft Learning Studio

Answer 1

Hi @Bala Narasimha Challa

You can try this function, it can meet your needs.

Create and populate a test table:

create table test (InputString varchar(max));
insert into test values
('microsoft learning studio'),
('HAPPY every DAY'),
('TOmOrrOw will bE beTTeR');

Create a custom function:

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

Output the result:

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

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Answer 2

Hi @Bala Narasimha Challa ,

Please try the following solution.

SQL

-- DDL and sample data poulation, start
DECLARE @tbl AS TABLE (ID INT IDENTITY PRIMARY KEY, words NVARCHAR(1024));
INSERT INTO @tbl (words)
VALUES ('WILLIAM FAULKNER'),
       ('microsoft learning studio'),
       ('EMILY DICKINSON');
-- DDL and sample data poulation, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT *
	, c.query('for $x in /root/r/text()
		return concat(upper-case(substring($x, 1, 1)), lower-case(substring($x, 2, 1024)))
		').value('.', 'NVARCHAR(1024)') AS Result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
      REPLACE(words, @separator, ']]></r><r><![CDATA[') + 
      ']]></r></root>' AS XML)) AS t1(c);

Output

+----+---------------------------+---------------------------+
| ID |           words           |          Result           |
+----+---------------------------+---------------------------+
|  1 | WILLIAM FAULKNER          | William Faulkner          |
|  2 | microsoft learning studio | Microsoft Learning Studio |
|  3 | EMILY DICKINSON           | Emily Dickinson           |
+----+---------------------------+---------------------------+

Answer 3

Where is yesterday's Yitzhak's answer in this thread? Why MS keeps changing interface from bad to worse? How can I now see all the threads I've been reading in SQL Server before?