字段中字符的截取
怎么从
(
ATO14
ATT14W1
AYE10B
AYH010B2H
AC11
AC115
AC115HC
AYCH245HIP
)中截取
结果变成：
ATO14
ATT14
AYE10
AYH010
AC11
AC115
AC115
AYCH245
[解决办法]
create table tb(col varchar(20))insert into tb values('ATO14')insert into tb values('ATT14W1')insert into tb values('AYE10B')insert into tb values('AYH010B2H')insert into tb values('AC11')insert into tb values('AC115')insert into tb values('AC115HC')insert into tb values('AYCH245HIP')gocreate function dbo.f_str(@col varchar(50)) returns varchar(50)asbegin declare @str varchar(50) set @str = '' declare @i as int set @i = 1 declare @j as int set @j = len(@col) declare @k as int set @k = 0 declare @l as int set @k = 0 while @i <= @j begin if substring(@col , @i , 1) between '0' and '9' begin if @i = @j begin set @str = @str + substring(@col , @l , @i - @l + 1) break end if @k = 0 begin set @k = 1 set @l = @i end end else begin if @k = 0 set @str = @str + substring(@col , @i , 1) else begin set @str = @str + substring(@col , @l , @i - @l) break end end set @i = @i + 1 end return @strendgo--调用函数select col , new_col = dbo.f_str(col) from tbdrop function dbo.f_strdrop table tb/*col new_col -------------------- -------------------------------------------------- ATO14 ATO14ATT14W1 ATT14AYE10B AYE10AYH010B2H AYH010AC11 AC11AC115 AC115AC115HC AC115AYCH245HIP AYCH245（所影响的行数为 8 行）*/
[解决办法]