正确/标题大小写 SQL Server 中具有异常的列表

我正在尝试将大写的列转换为正确的大小写，但有一些例外，如某些首字母缩略词、缩写。我正在按照以下代码来实现这一点。但看起来这将是一个持续的过程，因此，我想创建一个包含异常的表，以便于清理数据，并且我希望能够从函数调用异常表。如果有人可以帮助我处理他们拥有的任何与此类似的代码或有关如何实现它的任何想法，那就太好了。

ALTER FUNCTION [dbo].[Business_ProperCase]
     (@Text AS VARCHAR(8000)) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 
    -- declare some variables 
    DECLARE @Reset BIT; DECLARE @Ret VARCHAR(8000); DECLARE @i INT; 
    DECLARE @c0 CHAR(1); DECLARE @c1 CHAR(1); DECLARE @c2 CHAR(1); 
    DECLARE @CaseLen INT; 
    DECLARE @CaseExceptions VARCHAR(8000); 
    DECLARE @CaseValue VARCHAR(8000); 
    -- Set some default values 
    SELECT @Reset = 1, @i=1, @Ret = ''; 
    -- only apply if all characters are already in uppercase 
    IF (UPPER(@Text)=@Text COLLATE Latin1_General_CS_AI) 
         BEGIN 
                 -- add a leading and trailing space to indicate word delimiters (bol & eol) 
                 SET @Text = ' ' + @Text + ' '; 
                 -- cycle through each character, 
                 -- if non-alpha, uppercase next alpha character. 
                 -- if alpha then lowercase subsequent alphas. 
                 WHILE (@i <= LEN(@Text)) 
                         SELECT 
                                 @c0=SUBSTRING(@Text,@i-2,1), @c1=SUBSTRING(@Text,@i-1,1), @c2=SUBSTRING(@Text,@i,1), 
                                 @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c2) ELSE LOWER(@c2) END, 
                                 @Reset = CASE 
                                                                                             WHEN @c0 = ' ' AND @c1 = 'M' AND @c2 = 'c' THEN 1 
                                                 WHEN @c0 = ' ' AND @c1 IN ('D', 'I', 'O') AND @c2 = '''' THEN 1 

                                                 WHEN @c2 LIKE '[a-zA-Z'']' THEN 0               -- Apply LOWER to any character after alphas or apostrophes 
                                                ELSE 1                                                                  -- Apply UPPER to any character after symbols/punctuation 
                                         END, 
                                 @i = @i +1 
                 -- add a trailing space in case the previous rule changed this. 
                 SET @Ret = @Ret + ' '; 
                 -- custom exceptions: this search is case-insensitive and will 
                 -- replace the word to the case as it is written in the list. 
                 -- NOTE: this list has to end with a comma! 
                 SELECT @i=0, @CaseLen=0, 
                         @CaseExceptions = 'ABS,LLC,MD,MBA,MA,
--Want to create a table for these exceptions and call them from this function 
                 -- Loop through exception cases 
                 WHILE CHARINDEX(',', @CaseExceptions, @i+1)>0 
                         BEGIN 
                                 -- get the delimited word 
                                 SET @CaseLen = CHARINDEX(',', @CaseExceptions, @i+1) - @i 
                                 SET @CaseValue = SUBSTRING(@CaseExceptions, @i, @CaseLen) 
                                 -- replace it in the original text 
                                 SET @Ret = REPLACE(@Ret, ' '+@CaseValue+' ', ' '+@CaseValue+' ') 
                                 -- get position of next word 
                                 SET @i = CHARINDEX(',', @CaseExceptions, @i+@CaseLen) +1 
                         END 
                 -- remove any leading and trailing spaces 
                 SET @Ret = LTRIM(RTRIM(@Ret)); 
                 -- capitalize first character of data irrespective of previous rules 
                 SET @Ret = UPPER(SUBSTRING(@Ret,1,1)) + SUBSTRING(@Ret,2,LEN(@Ret)); 
         END 
    ELSE 
     BEGIN 
                 -- return the string unaffected if it is not in uppercase 
                 SET @Ret=@Text 
         END 
    RETURN @Ret 
 END

创建一个表（我使用 TITLE_CASE_EXCEPTION 作为我的示例），其中包含列 EXCEPTION然后它是从那里驱动的数据。

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[GUI].[fn_TITLE_CASE]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
        DROP FUNCTION [GUI].[fn_TITLE_CASE]
    GO
    CREATE FUNCTION [GUI].[fn_TITLE_CASE] 
        (
        @STRING VARCHAR(MAX)
        )
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
        SET QUOTED_IDENTIFIER OFF
        DECLARE @RESET BIT
        DECLARE @_OUT_STRING VARCHAR(MAX)
        DECLARE @I INT
        DECLARE @C CHAR(1)
        DECLARE @CASE_LEN INT = 0
        DECLARE @CASE_EXCEPTIONS VARCHAR(MAX) = ''
        DECLARE @CASE_VALUE VARCHAR(MAX) = ''
        IF @STRING IS NULL
            RETURN NULL
        IF @STRING = ''
            RETURN @STRING
        SELECT @STRING = LOWER(RTRIM(@STRING)), @RESET = 1, @I = 1, @_OUT_STRING = ''
        WHILE (@I <= LEN(@STRING))
        SELECT 
            @C = SUBSTRING(@STRING, @I, 1),
            @_OUT_STRING = @_OUT_STRING + CASE WHEN @RESET = 1 THEN UPPER(@C) ELSE @C END,
            @RESET = CASE WHEN @C LIKE '[a-zA-Z'']' THEN 0 ELSE 1 END,
            @I = @I + 1
        SELECT @I = 0, @_OUT_STRING = @_OUT_STRING + ' '
        SELECT @CASE_EXCEPTIONS = @CASE_EXCEPTIONS + RTRIM(EXCEPTION) + ',' FROM [LOOKUP].TITLE_CASE_EXCEPTION
        WHILE CHARINDEX(',', @CASE_EXCEPTIONS, @I + 1) > 0 
            BEGIN 
            -- get the delimited word 
            SET @CASE_LEN = CHARINDEX(',', @CASE_EXCEPTIONS, @I + 1) - @I
            SET @CASE_VALUE = SUBSTRING(@CASE_EXCEPTIONS, @I, @CASE_LEN) 
            -- replace it in the original text 
            SET @_OUT_STRING = REPLACE(@_OUT_STRING, ' ' + @CASE_VALUE + ' ', ' ' + @CASE_VALUE + ' ') 
            -- get position of next word 
            SET @I = CHARINDEX(',', @CASE_EXCEPTIONS, @I + @CASE_LEN) + 1 
            END
        RETURN RTRIM(@_OUT_STRING)
    END
    GO

下面是一个示例供您参考：

declare @s varchar(256) = 'This is a SQL test';
declare @t table (ignore varchar(256) not null);
insert into @t (ignore) values ('SQL');
declare @pos int = 1;
declare @nextpos int;
declare @w varchar(256);
while @pos <= len(@s)
begin
    set @nextpos = charindex(' ', @s + ' ', @pos);
    set @w = substring(@s, @pos, @nextpos - @pos);
    if not exists (select 1 from @t where ignore = @w)
        set @s = stuff(
            @s, @pos, @nextpos - @pos,
            stuff(lower(@w), 1, 1, upper(left(@w, 1)))
        );
    set @pos = @nextpos + 1;
    select @s;
end

要回答原始请求.. 设置一个表"异常"，其中包含单列 nvarchar （100）的 ConcatList，并将异常添加到此表中...然后创建一个视图以将它们连接在一起...

    create table exceptions (ConcatList nvarchar(100))
    create view [dbo].vExceptions
    as
     Select distinct
        substring(
        (
            Select ','+ up.ConcatList  AS [text()]
            From exceptions up 
            ORDER BY up.ConcatList
            For XML PATH ('')
        ), 2, 4000) [exceptions]
     From exceptions p

下面是问题中存储过程的略微增强版本。（虽然这是一个公认的不优雅的解决方案）来解释：

小写单词（of、the、an 等）
带连字符的首字母缩略词

紧跟在短划线或逗号之前或后面的异常。

alter FUNCTION [dbo].[Business_ProperCase]
     (@Text AS VARCHAR(8000)) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 
    -- declare some variables 
    DECLARE @Reset BIT; DECLARE @Ret VARCHAR(8000); DECLARE @i INT; 
    DECLARE @c0 CHAR(1); DECLARE @c1 CHAR(1); DECLARE @c2 CHAR(1); 
    DECLARE @CaseLen INT; 
    DECLARE @CaseExceptions VARCHAR(8000); 
    DECLARE @CaseValue VARCHAR(8000); 
    -- Set some default values 
    SELECT @Reset = 1, @i=1, @Ret = ''; 
    -- only apply if all characters are already in uppercase 
    IF (UPPER(@Text)=@Text COLLATE Latin1_General_CS_AI) 
         BEGIN 
                 -- add a leading and trailing space to indicate word delimiters (bol & eol) 
                 SET @Text = ' ' + @Text + ' '; 
                 -- cycle through each character, 
                 -- if non-alpha, uppercase next alpha character. 
                 -- if alpha then lowercase subsequent alphas. 
                 WHILE (@i <= LEN(@Text)) 
                         SELECT 
                                 @c0=SUBSTRING(@Text,@i-2,1), @c1=SUBSTRING(@Text,@i-1,1), @c2=SUBSTRING(@Text,@i,1), 
                                 @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c2) ELSE LOWER(@c2) END, 
                                 @Reset = CASE WHEN @c0 = ' ' AND @c1 = 'M' AND @c2 = 'c' THEN 1 
                                               WHEN @c0 = ' ' AND @c1 IN ('D', 'I', 'O') AND @c2 = '''' THEN 1 
                                               WHEN @c2 LIKE '[a-zA-Z'']' THEN 0               -- Apply LOWER to any character after alphas or apostrophes 
                                                ELSE 1                                         -- Apply UPPER to any character after symbols/punctuation 
                                          END, 
                                 @i = @i +1 
                 -- add a trailing space in case the previous rule changed this. 
                 SET @Ret = @Ret + ' '; 
                 -- custom exceptions: this search is case-insensitive and will 
                 -- replace the word to the case as it is written in the list. 
                 -- NOTE: this list has to end with a comma! 
                 SELECT @i=0, @CaseLen=0, 
                         @CaseExceptions = exceptions from vExceptions
--Want to create a table for these exceptions and call them from this function 
                 -- Loop through exception cases 
                 WHILE CHARINDEX(',', @CaseExceptions, @i+1)>0 
                         BEGIN 
                                 -- get the delimited word 
                                 SET @CaseLen = CHARINDEX(',', @CaseExceptions, @i+1) - @i 
                                 SET @CaseValue = SUBSTRING(@CaseExceptions, @i, @CaseLen) 
                                 if (@CaseValue = 'OF' or @CaseValue = 'AND' or @CaseValue ='THE' or @CaseValue='FOR')
                                 begin
                                     --replace with lower case 'of', 'and', 'the', 'for'
                                     SET @Ret = REPLACE(@Ret, ' '+@CaseValue+' ', ' '+lower(@CaseValue)+' ') 
                                 end
                                 else
                                 begin  
                                    if (CHARINDEX(' '+ @CaseValue +' ', @Ret)>0 )
                                    begin
                                         -- replace it in the original text 
                                         SET @Ret = REPLACE(@Ret, ' '+@CaseValue+' ', ' '+@CaseValue+' ') 
                                    end
                                    else if (CHARINDEX(' '+@CaseValue+',', @Ret)>0 )
                                    begin
                                        --replace text (with no spaces around it)
                                        SET @Ret = REPLACE(@Ret, ' '+@CaseValue+',', ' '+@CaseValue+',') 
                                    end
                                    else if (CHARINDEX(' '+@CaseValue+'-', @Ret)>0 )
                                    begin
                                        --replace text (with no spaces around it)
                                        SET @Ret = REPLACE(@Ret, ' '+@CaseValue+'-', ' '+@CaseValue+'-') 
                                    end
                                    else if (CHARINDEX('-'+@CaseValue+' ', @Ret)>0 )
                                    begin
                                        --replace text (with no spaces around it)
                                        SET @Ret = REPLACE(@Ret, '-'+@CaseValue+' ', '-'+@CaseValue+' ') 
                                    end
                                    else if (CHARINDEX(','+@CaseValue+' ', @Ret)>0 )
                                    begin
                                        --replace text (with no spaces around it)
                                        SET @Ret = REPLACE(@Ret, ','+@CaseValue+' ', '-'+@CaseValue+' ') 
                                    end
                                 end
                                 -- get position of next word 
                                 SET @i = CHARINDEX(',', @CaseExceptions, @i+@CaseLen) +1 
                         END 
                 -- remove any leading and trailing spaces 
                 SET @Ret = LTRIM(RTRIM(@Ret)); 
                 -- capitalize first character of data irrespective of previous rules 
                 SET @Ret = UPPER(SUBSTRING(@Ret,1,1)) + SUBSTRING(@Ret,2,LEN(@Ret)); 
         END 
    ELSE 
     BEGIN 
                 -- return the string unaffected if it is not in uppercase 
                 SET @Ret=@Text 
         END 
    RETURN @Ret 
 END

创建一个表（我使用 ExceptionsTable 作为我的示例），其中包含列WordExcepts .然后在页面顶部的最后一次DECLARE之后添加以下内容：

DECLARE @sql nvarchar(2000);
SET @sql = 'N select WordExcepts from ExceptionsTable'

然后在下面将您的例外调整为：

@CaseExceptions = @sql

只需根据需要添加到您的表中，它们就会从函数中过滤掉。

相关内容

最新更新

热门标签：