search the scripts section for "ProperCase" or "InitCaps" foir other examples, there's some out there that look for things like "O'Brian" and "St.James" or "David-Jones".

here's just one of many based on a Jeff Moden Enhancement concept:

Edit: previous function i posted was not working, swapping it for this one instead.

CREATE FUNCTION ProperCase(@OriginalText VARCHAR(8000))

RETURNS VARCHAR(8000)

BEGIN

DECLARE @CleanedText VARCHAR(8000)

;with

a1 as (select 1 as N union all select 1 union all

select 1 union all select 1 union all

select 1 union all select 1 union all

select 1 union all select 1 union all

select 1 union all select 1),

a2 as (select 1 as N from a1 as a cross join a1 as b),

a3 as (select 1 as N from a2 as a cross join a2 as b),

a4 as (select 1 as N from a3 as a cross join a2 as b),

Tally as (select top (len(@OriginalText)) row_number() over (order by N) as N from a4)

SELECT @CleanedText = ISNULL(@CleanedText,'') +

--first char is always capitalized?

CASE WHEN Tally.N = 1 THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))

WHEN SUBSTRING(@OriginalText,Tally.N -1,1) = ' ' THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))

ELSE LOWER(SUBSTRING(@OriginalText,Tally.N,1))

END

FROM Tally WHERE Tally.N <= LEN(@OriginalText)

RETURN @CleanedText

END

GO

select dbo.ProperCase('WHAT THE HECK IS GOIN ON AROUND HERE;')