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