Scalar函数处理字符串字面量与变量值结果不一致问题排查

问题现象 #

• 直接传入硬编码字符串调用函数，结果符合预期：

  select dbo.lf_titlecase('30600 N. Pima Rd. unit 16','street')
  

  返回：30600 N Pima Rd Unit 16（unit被转为大写Unit）

• 从t_address表读取字段调用函数时，结果不符合预期：

  select customer_no
      ,address_no
      ,street1
      ,dbo.lf_titlecase(street1,'street') "upd_street1"
  from t_address a
  where 
  customer_no = 174242
  

  返回的upd_street1值为：30600 N Pima Rd unit 16（unit未被转为大写）

验证信息 #

• 硬编码的字符串是直接从t_address表中复制的
• 执行相似度校验语句：

  SELECT DIFFERENCE ((SELECT street1 FROM T_ADDRESS WHERE address_no = 83212), '30600 N. Pima Rd. Lot 16')
  

  返回值为4，说明两个字符串相似度极高
• 手动将表中地址修改为30600 N. Pima Rd Unit 16后，函数输出恢复正确，推测句点是问题诱因，但硬编码带句点时无异常
• 该函数为标量函数，t_address表的street1字段类型为varchar(64)

函数核心代码 #

BEGIN
    DECLARE 
      @StrOut NVARCHAR(1024),
      @CurrentPosition INT,
      @NextSpace INT,
      @CurrentWord NVARCHAR(1024),
      @StrLen INT,
      @LastWord BIT
    SET @StrIn = case when @StrIn = '' then null else rtrim(@strIn) end
    SET @NextSpace = 1
    SET @CurrentPosition = 1
    SET @StrOut = ''
    SET @StrLen = LEN(@StrIn)
    SET @LastWord = 0

    WHILE @LastWord = 0
      BEGIN 
        SET @NextSpace = CHARINDEX(' ',isnull(@StrIn,' '), @CurrentPosition+ 1)
        IF  @NextSpace = 0 -- no more spaces found
          BEGIN
             SET @NextSpace = @StrLen
             SET @LastWord = 1
          END
        SET @CurrentWord = Upper(SUBSTRING(@StrIn, @CurrentPosition, 1)) 
        SET @CurrentWord = @CurrentWord + Lower(SUBSTRING(@StrIn, @CurrentPosition+1, @NextSpace - @CurrentPosition))
        --SET @CurrentWord = case 
                            --when @NextSpace = 0 and len(@CurrentWord) = 1
                            --then @CurrentWord + SUBSTRING(@StrIn, @CurrentPosition+1, @NextSpace - @CurrentPosition)
                            --else @CurrentWord + Lower(SUBSTRING(@StrIn, @CurrentPosition+1, @NextSpace - @CurrentPosition))
                            --end   
        SET @StrOut = @StrOut  + @CurrentWord
        SET @CurrentPosition = @NextSpace + 1
      END
    IF CHARINDEX(',',@StrOut)> 0 and @type = 'city' 
      BEGIN
        SET @StrOut = replace(@strout,',','')
      END

内容的提问来源于stack exchange，提问作者Zach Domrese