当前位置: 代码迷 >> Sql Server >> sql2005指定字段插入空格。该如何解决
  详细解决方案

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

热度:24   发布时间:2016-04-27 19:10:53.0
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
  相关解决方案