Finding Uppercase Character then Adding Space

Question

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

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

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

Answer 1

Create this function

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);