Capitalize the first letter of each word without affecting the consecutive letters

Question

I would like to make the first letter of each word capitalized. However, if the word next to the first letter is already capitalized, then it should not be affected.

For example:

text
AB test
ab test
Ab tEst

Desired output:

text
AB Test
Ab Test
Ab TEst 

When I tried with this query, even though it capitalized the first word, it also lowered the word next to it. For example, the below query goes from ABC test to Abc Test, and I would like to it to be ABC Test

SELECT INITCAP(text)
FROM table 

Any suggestions would be greatly appreciated.

Answer 1

This may help..

Create a function and use it in select.

CREATE FUNCTION dbo.InitialCap(@v AS VARCHAR(MAX))
RETURNS TABLE
AS
RETURN 
WITH a AS (
    SELECT (
        SELECT UPPER(LEFT(value, 1)) + (SUBSTRING(value, 2, LEN(value))) AS 'data()'
        FROM string_split(@v, ' ')
        ORDER BY CHARINDEX(value,@v)
        FOR XML PATH (''), TYPE) ret)

SELECT CAST(a.ret AS varchar(MAX)) ret from a

reference from https://stackoverflow.com/a/46344989/8077687

Answer 2

There is actually a redshift function that already does this:

INITCAP(string)

Example:

INITCAP('ab test')

will return:

Ab test