SQL Script (Uppercase & Propercase)

Is there one for Proper?

i will get back on that

only had a moment to comment via my phone

There isn't an built-in way to achieve "proper case" (or "Camel Case" or "CamelCaps")

The best way to achieve it is to use a user defined function and there are many references to these available.

Here is one from Scott Pletcher (see this previous question)

CREATE FUNCTION dbo.Proper_Case (
    @string varchar(2000)
    )
RETURNS varchar(2000)
AS
--Sample use: SELECT dbo.Proper_Case('aBC mike;a alkj/as 12k 43')
BEGIN

DECLARE @previousByte int
DECLARE @byte int

SET @string = LOWER(@string)
IF LEFT(@string, 1) LIKE '[a-z]'
    SET @string = STUFF(@string, 1, 1, UPPER(LEFT(@string, 1)))
SET @previousByte = 2

WHILE 1 = 1
BEGIN
    SET @byte = PATINDEX('%[^a-z0-9][a-z]%', SUBSTRING(@string, @previousByte, 2000))
    --PRINT 'Byte = ' + CAST(@byte AS varchar(10))
    IF @byte = 0
        BREAK
    SET @string = STUFF(@string, @previousByte + @byte, 1, 
        UPPER(SUBSTRING(@string, @previousByte + @byte, 1)))
    SET @previousByte = @previousByte + @byte + 1
    --PRINT 'PrevByte = ' + CAST(@previousByte AS varchar(10))
END --WHIILE

RETURN @string

END --FUNCTION

GO

Select allOpen in new window

For the following data

CREATE TABLE Table1
    ([Column1] varchar(60))
;
    
INSERT INTO Table1
    ([Column1])
VALUES
    ('patrick o''malley'),
    ('wolfgang von ribben tropp'),
    ('buckley & nunn'),
    ('archibald smyth-percival')
;

Select allOpen in new window

This query produced the following result:

SELECT
   dbo.Proper_Case(Column1)
FROM Table1
;


Patrick O'Malley
Wolfgang Von Ribben Tropp
Buckley & Nunn
Archibald Smyth-Percival

Select allOpen in new window