T-SQL 大写字母前的空格

Question

这个问题是不言自明的。您能指出一种在字符串的每个大写字母之间放置空格的方法吗？

SELECT dbo.SpaceBeforeCap('ThisIsATestString')

会导致

This Is A Test String.

Answer 1

仅当前一个和下一个字符为小写时才会添加空格。这样，“MyABCAnalysis”将成为“我的 ABC 分析”。

我也添加了对先前空间的检查。由于我们的一些字符串以 'GR_' 为前缀，有些还包含下划线，因此我们可以使用替换函数，如下所示：

select dbo.GR_SpaceBeforeCap(replace('GR_ABCAnalysis_Test','_',' '))
返回“GR ABC 分析测试”

CREATE FUNCTION GR_SpaceBeforeCap (
    @str nvarchar(max)
)
returns nvarchar(max)
as
begin

declare
    @i int, @j int
,   @cp nchar, @c0 nchar, @c1 nchar
,   @result nvarchar(max)

select
    @i = 1
,   @j = len(@str)
,   @result = ''

while @i <= @j
begin
    select
        @cp = substring(@str,@i-1,1)
    ,   @c0 = substring(@str,@i+0,1)
    ,   @c1 = substring(@str,@i+1,1)

    if @c0 = UPPER(@c0) collate Latin1_General_CS_AS
    begin
        -- Add space if Current is UPPER 
        -- and either Previous or Next is lower
        -- and Previous or Current is not already a space
        if @c0 = UPPER(@c0) collate Latin1_General_CS_AS
        and (
                @cp <> UPPER(@cp) collate Latin1_General_CS_AS
            or  @c1 <> UPPER(@c1) collate Latin1_General_CS_AS
        )
        and @cp <> ' '
        and @c0 <> ' '
            set @result = @result + ' '
    end -- if @co

    set @result = @result + @c0
    set @i = @i + 1
end -- while

return @result
end

Answer 2

假设 SQL Server 2005 或更高版本，此修改自此处的代码： http ://www.kodyaz.com/articles/case-sensitive-sql-split-function.aspx

CREATE FUNCTION SpaceBeforeCap
(
 @str nvarchar(max)
)
returns nvarchar(max)
as
begin

declare @i int, @j int
declare @returnval nvarchar(max)
set @returnval = ''
select @i = 1, @j = len(@str)

declare @w nvarchar(max)

while @i <= @j
begin
 if substring(@str,@i,1) = UPPER(substring(@str,@i,1)) collate Latin1_General_CS_AS
 begin
  if @w is not null
  set @returnval = @returnval + ' ' + @w
  set @w = substring(@str,@i,1)
 end
 else
  set @w = @w + substring(@str,@i,1)
 set @i = @i + 1
end
if @w is not null
 set @returnval = @returnval + ' ' + @w

return ltrim(@returnval)

end

然后可以按照您上面的建议进行调用。

Answer 3

该函数结合了之前的答案。有选择地选择保留相邻的 CAPS：

CREATE FUNCTION SpaceBeforeCap (
    @InputString NVARCHAR(MAX),
    @PreserveAdjacentCaps BIT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

DECLARE
    @i INT, @j INT,
        @previous NCHAR, @current NCHAR, @next NCHAR,
        @result NVARCHAR(MAX)

SELECT
    @i = 1,
        @j = LEN(@InputString),
        @result = ''

WHILE @i <= @j
BEGIN
    SELECT
        @previous = SUBSTRING(@InputString,@i-1,1),
                @current = SUBSTRING(@InputString,@i+0,1),
                @next = SUBSTRING(@InputString,@i+1,1)

    IF @current = UPPER(@current) COLLATE Latin1_General_CS_AS
    BEGIN
        -- Add space if Current is UPPER 
        -- and either Previous or Next is lower or user chose not to preserve adjacent caps
        -- and Previous or Current is not already a space
        IF @current = UPPER(@current) COLLATE Latin1_General_CS_AS
        AND (
                            @previous <> UPPER(@previous) COLLATE Latin1_General_CS_AS
                            OR  @next <> UPPER(@next) collate Latin1_General_CS_AS
                            OR  @PreserveAdjacentCaps = 0
        )
        AND @previous <> ' '
        AND @current <> ' '
            SET @result = @result + ' '
    END 

    SET @result = @result + @current
    SET @i = @i + 1
END 

RETURN @result
END

GO
SELECT dbo.SpaceBeforeCap('ThisIsASampleDBString', 1)
GO
SELECT dbo.SpaceBeforeCap('ThisIsASampleDBString', 0)

Answer 4

为了完全避免循环，使用计数表可以有所帮助。如果您在 SQL 2022 上运行，则 generate_series 函数甚至可以删除此依赖项。此方法将比循环迭代快得多。

create function core.ufnAddSpaceBeforeCapital
(
    @inputString nvarchar(max)
)
returns nvarchar(max)
as
begin

    declare @outputString nvarchar(max)

    select 
        @outputString = string_agg(iif(t.value = 1, upper(substring(@inputString,t.value,1)),iif(ascii(substring(@inputString,t.value,1)) between 65 and 90, ' ','') + substring(@inputString,t.value,1)),'')
    from 
        generate_series(1,cast(len(@inputString) as int)) t

    return @outputString

end

标量函数不可内联，因此如果您需要的话，我提供了替代的内联表值函数。

create function core.ufnAddSpaceBeforeCapitalITVF
(
    @inputString nvarchar(max)
)
returns table
as
return
(
    select 
        string_agg(iif(t.value = 1, upper(substring(@inputString,t.value,1)),iif(ascii(substring(@inputString,t.value,1)) between 65 and 90, ' ','') + substring(@inputString,t.value,1)),'') as outputString
    from 
        generate_series(1,cast(len(@inputString) as int)) t
)

end

Answer 5

CLR 和正则表达式或 26 个替换语句、区分大小写的整理子句和修剪。

Answer 6

另一种策略是检查每个字符的 ascii 值：

create function SpaceBeforeCap
 (@str nvarchar(max))
returns nvarchar(max)
as
begin
  declare @result nvarchar(max)= left(@str, 1), 
          @i int = 2

  while @i <= len(@str)
  begin
    if ascii(substring(@str, @i, 1)) between 65 and 90
      select @result += ' '
    select @result += substring(@str, @i, 1)
    select @i += 1 
  end

  return @result
 end

 /***
  SELECT dbo.SpaceBeforeCap('ThisIsATestString') 
 **/

Answer 7

虽然我真的很喜欢字符循环的答案，但我对性能并不感到兴奋。我发现对于我的用例来说，这只需一小部分时间即可完成。

CREATE function SpaceBeforeCap
    (@examine nvarchar(max))
returns nvarchar(max)
as
begin

DECLARE @index as INT

SET @index = PatIndex( '%[^ ][A-Z]%', @examine COLLATE Latin1_General_BIN)
WHILE @index > 0 BEGIN

    SET @examine = SUBSTRING(@examine, 1, @index) + ' ' + SUBSTRING(@examine, @index + 1, LEN(@examine))
    SET @index = PatIndex( '%[^ ][A-Z]%', @examine COLLATE Latin1_General_BIN)

END

RETURN LTRIM(@examine)

end

这利用了以下事实：
区分大小写的模式搜索仅适用于某些情况排序规则。字符类 [^ ] 表示除空格之外的任何内容，因此当我们将缺失的空格添加到字符串中时，我们会进一步匹配该字符串，直到它完整为止。