查找大写字符然后添加空格 [英] Finding Uppercase Character then Adding Space

问题描述

我购买了 SQL World City/State 数据库.在状态数据库中，它将状态名称推到了一起.示例:北卡罗来纳州"或南卡罗来纳州"...

I bought a SQL World City/State database. In the state database it has the state names pushed together. Example: "NorthCarolina", or "SouthCarolina"...

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

IS there a way in SQL to loop and find the uppercase characters and add a space???

这样北卡罗来纳州"就变成了北卡罗来纳州"???

this way "NorthCarolina" becomes "North Carolina"???

推荐答案

创建这个函数

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

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

Then you can use it to update your database

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

这篇关于查找大写字符然后添加空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！