日期:2014-05-18 浏览次数:20627 次
create table tb(id int,[value] varchar(100)) insert into tb select 1,'HelloWorld' union all select 2,'NewArrivalsCareerClothing' union all select 3,'CheckbookCoversCheckbookCovers' union all select 4,'RetroHandbagsConvertibleBagsZeros' go create function f_change(@a varchar(100)) returns varchar(100) as begin declare @len int declare @int int declare @str varchar(100) set @str = '' set @len = len(@a) set @int = 1 while @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 end return stuff(@str,1,1,'') end go select * from tb select id,dbo.f_change([value]) [value] from tb drop table tb drop function f_change id value ----------- ---------------------------------------------------------------- 1 HelloWorld 2 NewArrivalsCareerClothing 3 CheckbookCoversCheckbookCovers 4 RetroHandbagsConvertibleBagsZeros (4 行受影响) id value ----------- ---------------------------------------------------------------- 1 Hello World 2 NewArrivals Career Clothing 3 Checkbook Covers Checkbook Covers 4 Retro Handbags Convertible Bags Zeros (4 行受影响)
------解决方案--------------------
declare @i int set @i=65 while @i<=90 begin update T set [value]= replace(ltrim(replace([value],char(@i) COLLATE Chinese_PRC_CS_AS,' '+char(@i))),space(2),space(1)) set @i=@i+1 end
------解决方案--------------------
alter function f_t(@word varchar(1000)) returns varchar(1000) as begin 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 @r end select dbo.f_t('NewArrivalsCareerClothing') /* ----------------------------- New Arrivals Career Clothing */
------解决方案--------------------
create function getname (@string varchar(100)) returns varchar(1000) as begin 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 @string2 end select dbo.getname('NewArrivalsCareerClothing') --------------- New Arrivals Career Clothing