How to fix all caps to normal case-CodePudding

I have a sql table that contains an employee's full name in all caps (i.e. SMITH-EASTMAN,JIM M). I need to be able to separate the full name into two separate columns (Last Name and First Name). This part is going well. Now I could use some assistance with removing the capital letters and putting it in normal case.

How can I take the results of my common table expression and pass them into a function?

WITH CTE AS
(
    SELECT FullName = [Employee Name],
           LastName = SUBSTRING([Employee Name], 1, CHARINDEX(',',[Employee Name])-1),             
           FirstNameStartPos = CHARINDEX(',',[Employee Name])   1,
           MidlleInitialOrFirstNameStartPos = CHARINDEX(' ',[Employee Name]),
           MiddleInitialOrSecondFirstName = SUBSTRING([Employee Name], CHARINDEX(' ',[Employee Name]),LEN([Employee Name])),
           MiddleInitialOrSecondFirstNameLen = LEN(SUBSTRING([Employee Name], CHARINDEX(' ',[Employee Name]),LEN([Employee Name]))) - 1       
    FROM ['Med-PS PCN Mapping$']    
    WHERE [PS Employee ID] IS NOT NULL  
),
CTE2 AS
(
    SELECT FullName = CTE.FullName,
           DerivedFirstName = CASE
                                WHEN CTE.MiddleInitialOrSecondFirstNameLen = 1 
                                  THEN SUBSTRING(CTE.FullName, CTE.FirstNameStartPos, CTE.MidlleInitialOrFirstNameStartPos - CTE.FirstNameStartPos)
                                ELSE SUBSTRING(CTE.FullName, CTE.FirstNameStartPos, CTE.FirstNameStartPos   CTE.MiddleInitialOrSecondFirstNameLen)
                              END,
           DerivedLastName = CTE.LastName                         
    FROM CTE
)
SELECT * 
FROM CTE2

RESULTS

FullName                DerivedFirstName    DerivedLastName
SMITH-EASTMAN,JIM M     JIM                 SMITH-EASTMAN
O'DAY,MARTIN C          MARTIN              O'DAY
TROUT,MADISON MARIE     MADISON MARI        TROUT



CREATE FUNCTION [dbo].[FixCap] ( @InputString varchar(4000) ) 
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(255)

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].[FixCap] (pass in DerivedFirstName from CTE2);

select [dbo].[FixCap] (pass in DerivedLastName from CTE2);

CodePudding user response：

Do you want something like INITCAP ?

CREATE FUNCTION dbo.F_INITCAP (@PHRASE NVARCHAR(max))
RETURNS NVARCHAR(max)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
   IF LEN(@PHRASE) < 1 RETURN @PHRASE;
   DECLARE @I INT = 1, @C CHAR(1), @P BIT = 0, @OUT VARCHAR(max) = '';
   WHILE @I <= LEN(@PHRASE)
   BEGIN
      SET @C = SUBSTRING(@PHRASE, @I, 1);
      IF @C BETWEEN 'A' AND 'Z' COLLATE Latin1_General_CI_AI
      BEGIN
         IF @P = 0
            SET @OUT = @OUT   UPPER(@C);
         ELSE  
            SET @OUT = @OUT   LOWER(@C);
         SET @P = 1
      END
      ELSE
      BEGIN
         SET @P = 0;
         SET @OUT = @OUT   LOWER(@C);
      END
      SET @I = @I   1;
   END
   RETURN @OUT;
END
GO