谁能把下面的这个函数转为SQL函数?

[AmeKenVB]

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

[langziqian]

应该不难啊 有时间搞搞

[nightmaple]

这样？

create function dfdf(@sjno varchar(200))
returns varchar(200)
as
begin
declare @strtemp varchar(20),@i int,@temp varchar(200)
If Len(@sjno) > 0 Then
	set @temp=ASCII(upper(Left(sjno, 1)))
    If @temp >= 65 And @temp <= 90 Then
        If Len(@sjno) > 6 Then
            If substring(@sjno, 2, 1) > 0 Then
                set @strtemp = Left(@sjno, 1)+substring(@sjno, 2, 6)
            Else if substring(sjno, 3, 1) > 0 Then
                set @strtemp = Left(@sjno, 1)+substring(@sjno, 3, 5)
            Else if substring(sjno, 3, 1) = 0 Then
                set @strtemp = Left(@sjno, 1)+substring(@sjno, 4, 4)
     else
        @strtemp = @sjno
Else if IsNull(@sjno,'')='' Then
	set @strtemp='0'
return @strtemp
end
go

[Sharon_liu]

函数写的乱七八糟，功能也不明，给你直译了，对不对不管了

CREATE FUNCTION DFDF(@SJNO AS VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @STRTEMP AS VARCHAR(8000),@I AS BIGINT,@TEMP AS VARCHAR(8000)
IF LEN(@SJNO)>0 
BEGIN
SELECT @TEMP=ASCII(UPPER(LEFT(@SJNO,1)))
IF @TEMP BETWEEN 65 AND 90 
BEGIN
IF LEN(@SJNO)>6
BEGIN
IF SUBSTRING(@SJNO,2,1)>0 
RETURN LEFT(@SJNO,1)+SUBSTRING(@SJNO,2,6)
ELSE
IF SUBSTRING(@SJNO,3,1)>0 
RETURN LEFT(@SJNO,1)+SUBSTRING(@SJNO,3,5)
ELSE
IF SUBSTRING(@SJNO,3,1)=0
RETURN LEFT(@SJNO,1)+SUBSTRING(@SJNO,4,4)
END
ELSE
RETURN @SJNO
END
ELSE
IF @SJNO IS NULL
RETURN '0'
END
END

[ChinaJiaBing]

case when 

[nightmaple]

少了一个begin...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 Then
	set @temp=ASCII(upper(Left(sjno, 1)))
    If @temp >= 65 And @temp <= 90 Then
        If Len(@sjno) > 6 Then
        begin
			If substring(@sjno, 2, 1) > 0 Then
				set @strtemp = Left(@sjno, 1)+substring(@sjno, 2, 6)
			Else if substring(sjno, 3, 1) > 0 Then
				set @strtemp = Left(@sjno, 1)+substring(@sjno, 3, 5)
			Else if substring(sjno, 3, 1) = 0 Then
				set @strtemp = Left(@sjno, 1)+substring(@sjno, 4, 4)
	    end
     else
        @strtemp = @sjno
Else if IsNull(@sjno,'')='' Then
	set @strtemp='0'
return @strtemp
end
go

[nightmaple]

#5的 then全部不要。。。我都弄混了。。。
[Quote=引用 5 楼 nightmaple 的回复:]
少了一个begin...end

SQL code
create function dfdf(@sjno varchar(200))
returns varchar(200)
as
begin
declare @strtemp varchar(20),@i int,@temp varchar(200)
If Len(@sjno) > 0 Then
set @temp=AS……
[/Quote]

[AmeKenVB]

谢谢,我调试下

[nightmaple]

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 
        begin
			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)
	    end
     else
        @strtemp = @sjno
Else if IsNull(@sjno,'')='' 
	set @strtemp='0'
return @strtemp
end
go

[AmeKenVB]

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

我搞好了,谢谢你们,你们太强悍了

[ChinaJiaBing]

--函数

create function dfdf
(
  @sjno nvarchar(100)
)
returns nvarchar(4000)
as
begin
declare @strtemp nvarchar(4000), @temp nvarchar(4000)
if len(@sjno)>0
begin
set @temp=Ascii(upper(left(@sjno,1)))
if @temp>=65 and @temp<=90
begin
if len(@sjno)>6
begin
 if substring(@sjno,2,1)>0
 set  @strtemp=left(@sjno,1)+substring(@sjno,2,6)
 if substring(@sjno,3,1)>0
    set @strtemp=left(@sjno,1)+substring(@sjno,3,5)
 if substring(@sjno,3,1)=0
   set @strtemp=left(@sjno,1)+substring(@sjno,4,4)
end   
end
end
return @strtemp
end