Public Function dfdf(sjno As String) As String '编号改为4位
Dim strtemp As String, i As Long, temp As String
If Len(sjno) > 0 Then
temp = Asc(UCase(Left(sjno, 1)))
If temp >= 65 And temp <= 90 Then
If Len(sjno) > 6 Then
If Mid(sjno, 2, 1) > 0 Then
strtemp = Left(sjno, 1) & Mid(sjno, 2, 6)
ElseIf Mid(sjno, 3, 1) > 0 Then
strtemp = Left(sjno, 1) & Mid(sjno, 3, 5)
ElseIf Mid(sjno, 3, 1) = 0 Then
strtemp = Left(sjno, 1) & Mid(sjno, 4, 4)
End If
End If
Else
strtemp = sjno
End If ElseIf IsNull(sjno) = True Then
dfdf = 0
End If
dfdf = strtemp
End Function
returns nvarchar(1000)
as
begin
declare @strtemp int
declare @i int
declare @temp nvarchar(100)
If Len(@sjno) > 0
@temp = ascii(Asc(Left(sjno, 1)))
If @temp >= 65 And @temp <= 90
If Len(@sjno) > 6
If cast(substring(@sjno, 2, 1) as int) > 0
@strtemp = cast(Left(@sjno, 1)as int) & cast(substring(@sjno, 2, 6) as int)
Else If cast(substring(@sjno, 3, 1) as int) < 0
@strtemp =cast( Left(@sjno, 1)as int) & cast(substring(@sjno, 3, 5) as int)
Else If cast(substring(@sjno, 3, 1) == 0
@strtemp = cast(Left(@sjno, 1)as int) & cast(substring(@sjno, 4, 4) as int)
End If
End If
Else
@strtemp = @sjno
End If Else If @sjno is null
return 0
End If
return @strtemp
End没测试 自己在改一下
returns nvarchar(1000)
as
begin
declare @strtemp int
declare @i int
declare @temp nvarchar(100)
If Len(@sjno) > 0
select @temp = ascii(Left(@sjno,1))
If @temp >= 65 And @temp <= 90
If Len(@sjno) > 6
If cast(substring(@sjno, 2, 1) as int) > 0
select @strtemp = cast(Left(@sjno, 1)as int) & cast(substring(@sjno, 2, 6) as int)
Else If cast(substring(@sjno, 3, 1) as int) < 0
select @strtemp =cast( Left(@sjno, 1)as int) & cast(substring(@sjno, 3, 5) as int)
Else If cast(substring(@sjno, 3, 1)as int) = 0
select @strtemp = cast(Left(@sjno, 1)as int) & cast(substring(@sjno, 4, 4) as int) Else
select @strtemp = @sjno
Else If @sjno is null
return 0 return @strtemp
End编译通过 运行就看你的造化了
create function dfdf(@sjno varchar(200))
returns varchar(200)
as
begin
declare @strtemp varchar(20),@i int,@temp varchar(200)
If Len(@sjno) > 0
set @temp=ASCII(upper(Left(@sjno, 1)))
If @temp >= 65 And @temp <= 90
If Len(@sjno) > 6
If substring(@sjno, 2, 1) > 0
set @strtemp = Left(@sjno, 1)+substring(@sjno, 2, 6)
Else if substring(@sjno, 3, 1) > 0
set @strtemp = Left(@sjno, 1)+substring(@sjno, 3, 5)
Else if substring(@sjno, 3, 1) = 0
set @strtemp = Left(@sjno, 1)+substring(@sjno, 4, 4)
else
Set @strtemp = @sjno
Else if IsNull(@sjno,'')=''
set @strtemp='0'
return @strtemp
end
go
弄好了,揭贴