sql - 查找大写字符然后添加空格

Question

我买了一个 SQL World City/State 数据库。在州数据库中，它把州名放在一起。示例:“北卡罗来纳州”或“南卡罗来纳州”...

SQL 中有没有办法循环查找大写字符并添加空格？？？

这样“NorthCarolina”就变成了“North Carolina”???

Answer 1

创建这个函数

if object_id('dbo.SpaceBeforeCaps') is not null
    drop function dbo.SpaceBeforeCaps
GO
create function dbo.SpaceBeforeCaps(@s varchar(100)) returns varchar(100)
as
begin
    declare @return varchar(100);
    set @return = left(@s,1);
    declare @i int;
    set @i = 2;
    while @i <= len(@s)
    begin
        if ASCII(substring(@s,@i,1)) between ASCII('A') and ASCII('Z')
            set @return = @return + ' ' + substring(@s,@i,1)
        else
            set @return = @return + substring(@s,@i,1)
        set @i = @i + 1;
    end;
    return @return;
end;
GO

然后你可以用它来更新你的数据库

update tbl set statename = select dbo.SpaceBeforeCaps(statename);