如何将所有大写字母固定为正常大小写

Question

我有一个sql表，其中包含所有大写的员工全名(例如SMITH-EASTMAN,JIM M)。我需要能够把全名分成两个单独的列(姓和名)。这部分进展顺利。现在我需要一些帮助，把大写字母去掉，改成正常的大小写。

我怎么能把我的公共表表达式的结果，并将它们传递到一个函数?

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

结果

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);

Answer 1

你想要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