select CONVERT(int,[L1]) from [table]where [L1]=N'201'--加上N
select CONVERT(int,[L1]) from [table]where [L1]=cast(N'201' as int)--加上N
select CONVERT(int,[L1]) from [table]where Ltrim([L1])=N'201'--加上N
create function dbo.f_GetNumeric( @strValue as varchar(800))returns varchar(800) as begin declare @Result as varchar(800), @Temp as varchar(2), @i as int, @flag as int, @varbin as int set @Result='' if len(@strValue)<=0 begin return '空' end set @i=1 while @i<=Len(@strValue) begin set @Temp=Substring(@strValue,@i,1) If ascII(@Temp)<=57 and ascII(@Temp)>=48 --判断是不是0—9数字,而IsNumeric()函数的实际作用是判断参数表达式是否是数值, --而这个所谓的“数值”不仅仅包含普通的数字 begin set @Result=@Result+@Temp set @flag=0 end else begin set @varbin=cast(cast(@Temp as varbinary) as int) if @varbin<=41913 and @varbin>=41904 --判断是不是全角的0-9,全角0-9转换为varbinary再转换为int后值为41904-41913 begin set @varbin=@varbin-41856 --转换为半角,半角0-9的ascII值为48-57 set @Result=@Result+char(@varbin) set @flag=0 end else if @flag=0 begin set @Result=@Result+'-' set @flag=1 end end set @i=@i+1 end if right(@Result,1)='-' begin set @Result=left(@Result,len(@Result) -1) end return @Result endgoselect dbo.f_GetNumeric('201')/*--- 201
求句子 select CONVERT(int,[L1]) from [table]where [L1]='201'
主要原因是CONVERT(int,[L1]) 这个无法转换,所以: 简单的: select CONVERT(int, replace(replace(Replace([L1],'2','2'),'0','0'),'1','1') ) from [table]where [L1]='201' 当然更可以用ssp2009的函数: select CONVERT(int,dbo.f_GetNumeric([L1])) from [table]where [L1]='201'
use tempdb GO if object_id('fn_Convert') Is Not Null Drop function fn_Convert Go Create function fn_Convert ( @Str nvarchar(50) ) returns int As Begin Declare @ConertList nchar(10), @Index smallint Set @ConertList=N'0123456789' Set @Index=len(@Str) While @Index>0 Begin if isnumeric(substring(@Str,@Index,1))=0 Set @Str=replace(@Str,substring(@Str,@Index,1),nullif(Charindex(substring(@Str,@Index,1),@ConertList)-1,-1)) Set @Index= @Index-1 End if isnumeric(@Str)=0 set @Str=null Return @Str End Go print dbo.fn_Convert(N'583') --583print dbo.fn_Convert(N'-06789') --6789 print dbo.fn_Convert(N'-16237') --16237print dbo.fn_Convert(N'的632') --null
update [table] set L1=replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(L1,'0','0'),'1','1'),'2',2),'3','3'),'4','4'),'5','5'),'6','6'),'7','7'),'8','8'),'9','9')
select CONVERT(int,[L1]) from [table]where [L1]=N'201'--加上N
select CONVERT(int,[L1]) from [table]where [L1]=cast(N'201' as int)--加上N
select CONVERT(int,[L1]) from [table]where Ltrim([L1])=N'201'--加上N
201
select CONVERT(int,[L1]) from [table]where [L1]='201'
简单的:
select CONVERT(int,
replace(replace(Replace([L1],'2','2'),'0','0'),'1','1')
) from [table]where [L1]='201'
当然更可以用ssp2009的函数:
select CONVERT(int,dbo.f_GetNumeric([L1])) from [table]where [L1]='201'
GO
if object_id('fn_Convert') Is Not Null
Drop function fn_Convert
Go
Create function fn_Convert
(
@Str nvarchar(50)
)
returns int
As
Begin
Declare @ConertList nchar(10),
@Index smallint
Set @ConertList=N'0123456789'
Set @Index=len(@Str)
While @Index>0
Begin
if isnumeric(substring(@Str,@Index,1))=0
Set @Str=replace(@Str,substring(@Str,@Index,1),nullif(Charindex(substring(@Str,@Index,1),@ConertList)-1,-1))
Set @Index= @Index-1
End
if isnumeric(@Str)=0 set @Str=null
Return @Str
End
Go
print dbo.fn_Convert(N'583')
--583print dbo.fn_Convert(N'-06789')
--6789
print dbo.fn_Convert(N'-16237')
--16237print dbo.fn_Convert(N'的632')
--null