Add two numeric strings to generate a new string.

/*
-- Getdate () cannot be used in functions like this.
-- Add numeric characters to generate new numeric string data, and add their own prefix. This can be used to generate an idnumber. The generated numeric string will not exceed the integer length range.

Select DBO. createdifferentid ('mm', '000000', '45', 'varchar ') --- generate mm0045

*//*
Created by: hopewell_go
Reference to retain this */
Create Function createdifferentid (@ vprestring varchar (50), @ vstartstring varchar (250), @ vsetid varchar (200), @ vedittype varchar (20 ))
Returns varchar (250)
As
Begin
Declare @ vreturnvalue varchar (250)
, @ Vremnantstring varchar (250)
, @ Iprechar int
, @ Isetchar int
, @ Iaddchar int
, @ Iprelen int
, @ Isetlen int
, @ Iprepos int
, @ Isetpos int
, @ Ifeedinbit int
, @ Iendlen int
, @ Vtemp varchar (20)

Select @ vreturnvalue =''
, @ Vremnantstring =''
, @ Iprelen = Len (ltrim (rtrim (@ vstartstring )))
, @ Iprechar = NULL
, @ Iprepos = 1
, @ Iaddchar = 0
, @ Isetlen = Len (ltrim (rtrim (@ vsetid )))
, @ Isetpos = 1
, @ Ifeedinbit = 0
, @ Iendlen = 0
, @ Vtemp =''
 
-- Implement the string ID, such as 0000000
If (@ vedittype = 'varchar ')
Begin
If (@ iprelen <@ isetlen) -- exchange data, that is, the long string is in front and the short string is in the back.
Begin
Select @ vtemp = @ vstartstring
, @ Vstartstring = @ vsetid
, @ Vsetid = @ vtemp
, @ Vtemp = @ iprelen
, @ Iprelen = @ isetlen
, @ Isetlen = @ vtemp
End
While (@ iprepos <= @ iprelen)
Begin
Set @ iprechar = substring (@ vstartstring, @ iprelen-@ iprepos + 1, 1)
If (@ isetpos <= @ isetlen)
Begin
Select @ isetchar = substring (@ vsetid, @ isetlen-@ iprepos + 1, 1) + @ ifeedinbit
, @ Iaddchar = @ iprechar + @ isetchar
, @ Ifeedinbit = @ iaddchar/10
, @ Vreturnvalue = cast (@ iaddchar % 10 as varchar) + @ vreturnvalue
Set @ iendlen = @ iprepos
End
Else
Begin
If (@ ifeedinbit = 1) -- carry must be added when there is an increment.
Begin
Select @ iaddchar = @ iprechar + @ ifeedinbit
, @ Ifeedinbit = @ iaddchar/10
, @ Vreturnvalue = cast (@ iaddchar % 10 as varchar) + @ vreturnvalue
If (@ ifeedinbit = 0)
Begin
Set @ iendlen = @ iprepos
Break
End
Else if (@ iprepos = @ iprelen) -- process the last carry
Begin
Select @ vreturnvalue = '1' + @ vreturnvalue
, @ Iendlen = @ iprelen
Break
End
End
Else -- end without carry
Begin
Break
End
End
Select @ iprepos = @ iprepos + 1
, @ Isetpos = @ isetpos + 1
End
Select @ vremnantstring = left (@ vstartstring, @ iprelen-@ iendlen)
, @ Vreturnvalue = @ vprestring + @ vremnantstring + @ vreturnvalue
End
-- Return (@ ifeedinbit)
/*
Else if (@ vedittype = 'Year ')
Begin

Select @ vreturnvalue = DBO. createdifferentid (@ vprestring, @ vstartstring, @ vsetid, 'varchar ')
, @ Vreturnvalue = Replace (@ vreturnvalue, @ vprestring, @ vprestring + '05 ')
End
*/
Return (@ vreturnvalue)

End