sql2005指定字段插入空格。该如何解决

sql2005指定字段插入空格。
Id value
   
  1 HelloWorld
  2 NewArrivalsCareerClothing
  3 CheckbookCoversCheckbookCovers
  4 RetroHandbagsConvertibleBags

 要更新成：
   
  1 Hello World
  2 New Arrivals Career Clothing
  3 Checkbook Covers Checkbook Covers
  4 Retro Handbags Convertible Bags

根据大写字母前插入一个空格。


[解决办法]

SQL code

create table tb(id int,[value] varchar(100))insert into tbselect 1,'HelloWorld' union allselect 2,'NewArrivalsCareerClothing' union allselect 3,'CheckbookCoversCheckbookCovers' union allselect 4,'RetroHandbagsConvertibleBagsZeros'gocreate function f_change(@a varchar(100))returns varchar(100)asbegindeclare @len intdeclare @int intdeclare @str varchar(100)set @str = ''set @len = len(@a)set @int = 1while @int <= @len    begin        if (ascii(substring(@a,@int,1)) between 66 and 96)        begin            set @str = @str + ' ' + substring(@a,@int,1)        end        else        begin            set @str = @str + substring(@a,@int,1)        end        set @int = @int + 1    endreturn stuff(@str,1,1,'')endgoselect * from tbselect id,dbo.f_change([value]) [value] from tbdrop table tbdrop function f_changeid          value----------- ----------------------------------------------------------------1           HelloWorld2           NewArrivalsCareerClothing3           CheckbookCoversCheckbookCovers4           RetroHandbagsConvertibleBagsZeros(4 行受影响)id          value----------- ----------------------------------------------------------------1           Hello World2           NewArrivals Career Clothing3           Checkbook Covers Checkbook Covers4           Retro Handbags Convertible Bags Zeros(4 行受影响)
[解决办法]
SQL code
declare @i intset @i=65while @i<=90begin  update T   set [value]= replace(ltrim(replace([value],char(@i) COLLATE Chinese_PRC_CS_AS,' '+char(@i))),space(2),space(1))     set @i=@i+1end
[解决办法]
SQL code
alter function f_t(@word varchar(1000))returns varchar(1000)asbegin    declare @v char(1)    declare @l nvarchar(1000)    declare @r nvarchar(1000)    --set @v=LEFT(@word,1)    --if @v collate Chinese_PRC_CS_AS_WS in ('ABCDEFGHIJKLMNOPQRSTUVWXYZ')    --    set @r+=' '+@v    --else    --begin    --    set @word=RIGHT(@word,LEN(@word)-1)    --end    set @r=''    while LEN(@word)>0    begin        set @v=LEFT(@word,1)        if charindex(@v collate Chinese_PRC_CS_AS_WS ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ')>0            set @r+=' '+@v        else            set @r+=@v        set @word=RIGHT(@word,LEN(@word)-1)    end    return @rendselect dbo.f_t('NewArrivalsCareerClothing')/*----------------------------- New Arrivals Career Clothing */
[解决办法]
SQL code
create function getname (@string varchar(100))returns varchar(1000)asbegin      DECLARE @position int,@string2 varchar(1000)   set @position=1   set @string2=''     WHILE @position<len(@string)+1        begin          if(ASCII(SUBSTRING(@string, @position, 1))<96)           begin             set @string2=@string2+' '+SUBSTRING(@string, @position, 1)           end         else            begin             set @string2=@string2+SUBSTRING(@string, @position, 1)            end         set @position=@position+1        end    return @string2endselect dbo.getname('NewArrivalsCareerClothing')---------------New Arrivals Career Clothing 
 
[解决办法]
楼主的问题可以用正则表达式解决,速度应该是最快的。
SQL code
--以下是我以前写的利用正则表达式替换字符串的函数IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL     DROP FUNCTION dbo.RegexReplaceGOCREATE FUNCTION dbo.RegexReplace(    @string VARCHAR(MAX),    --被替换的字符串    @pattern VARCHAR(255),    --替换模板    @replacestr VARCHAR(255),    --替换后的字符串    @IgnoreCase INT = 0    --0区分大小写 1不区分大小写)RETURNS VARCHAR(MAX)AS BEGIN    DECLARE @objRegex INT, @retstr VARCHAR(8000)    --创建对象    EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT    --设置属性    EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern    EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase    EXEC sp_OASetProperty @objRegex, 'Global', 1    --执行    EXEC sp_OAMethod @objRegex, 'Replace', @retstr OUT, @string, @replacestr    --释放    EXECUTE sp_OADestroy @objRegex    RETURN @retstrENDGO--针对这里，可以：SELECT dbo.RegexReplace([value],'[A-Z]',' $&',0) from tb/*--------------------------------------- Hello World New Arrivals Career Clothing Checkbook Covers Checkbook Covers Retro Handbags Convertible Bags Zeros(4 行受影响)*/
[解决办法]
如果楼主的字符串中有CPU,WC,CCTV,WTO等类似数据呢？