如何在插入前增加varchar值的一部分

本文介绍了如何在插入前增加varchar值的一部分的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 大家好， 我必须在sql预插入存储过程中增加字段的varchar值的一部分。值的第一部分将是年和月，剩余部分将是0001,0002 .....必须在存储过程中递增。将值0001转换为int或numberic后，它将变为1位数。有人可以帮帮我吗？ 我尝试了什么： Hi All,I have to increment a part of varchar value of field in sql pre insert stored procedure. The first portion of the value will be year and month and remaining portion will be 0001, 0002..... which has to increment in stored procedure. After converting value 0001 into int or numberic it's getting as 1 digit. Can anyone help me please?What I have tried:Alter procedure [dbo].[taPMTransactionInsertPre] @I_vBACHNUMB char(15) output, @I_vVCHNUMWK char(17) output, @I_vVENDORID char(15) output, @I_vDOCNUMBR char(20) output, @I_vDOCTYPE smallint output, @I_vDOCAMNT numeric(19,5) output, @I_vDOCDATE datetime output, @I_vPSTGDATE datetime output, @I_vVADCDTRO char(15) output, @I_vVADDCDPR char(15) output, @I_vPYMTRMID char(20) output, @I_vTAXSCHID char(15) output, @I_vDUEDATE datetime output, @I_vDSCDLRAM numeric(19,5) output, @I_vDISCDATE datetime output, @I_vPRCHAMNT numeric(19,5) output, @I_vCHRGAMNT numeric(19,5) output, @I_vCASHAMNT numeric(19,5) output, @I_vCAMCBKID char(15) output, @I_vCDOCNMBR char(20) output, @I_vCAMTDATE datetime output, @I_vCAMPMTNM char(20) output, @I_vCHEKAMNT numeric(19,5) output, @I_vCHAMCBID char(15) output, @I_vCHEKDATE datetime output, @I_vCAMPYNBR char(20) output, @I_vCRCRDAMT numeric(19,5) output, @I_vCCAMPYNM char(20) output, @I_vCHEKNMBR char(20) output, @I_vCARDNAME char(15) output, @I_vCCRCTNUM char(20) output, @I_vCRCARDDT datetime output, @I_vCHEKBKID char(15) output, @I_vTRXDSCRN char(30) output, @I_vTRDISAMT numeric(19,5) output, @I_vTAXAMNT numeric(19,5) output, @I_vFRTAMNT numeric(19,5) output, @I_vTEN99AMNT numeric(19,5) output, @I_vMSCCHAMT numeric(19,5) output, @I_vPORDNMBR char(20) output, @I_vSHIPMTHD char(15) output, @I_vDISAMTAV numeric(19,5) output, @I_vDISTKNAM numeric(19,5) output, @I_vAPDSTKAM numeric(19,5) output, @I_vMDFUSRID char(15) output, @I_vPOSTEDDT datetime output, @I_vPTDUSRID char(15) output, @I_vPCHSCHID char(15) output, @I_vFRTSCHID char(15) output, @I_vMSCSCHID char(15) output, @I_vPRCTDISC numeric(19,2) output, @I_vTax_Date datetime output, @I_vCURNCYID char(15) output, @I_vXCHGRATE numeric(19,7) output, @I_vRATETPID char(15) output, @I_vEXPNDATE datetime output, @I_vEXCHDATE datetime output, @I_vEXGTBDSC char(30) output, @I_vEXTBLSRC char(50) output, @I_vRATEEXPR smallint output, @I_vDYSTINCR smallint output, @I_vRATEVARC numeric(19,7) output, @I_vTRXDTDEF smallint output, @I_vRTCLCMTD smallint output, @I_vPRVDSLMT smallint output, @I_vDATELMTS smallint output, @I_vTIME1 datetime output, @I_vBatchCHEKBKID char(15) output, @I_vCREATEDIST smallint output, @I_vRequesterTrx smallint output, @I_vUSRDEFND1 char(50) output, @I_vUSRDEFND2 char(50) output, @I_vUSRDEFND3 char(50) output, @I_vUSRDEFND4 varchar(8000) output, @I_vUSRDEFND5 varchar(8000) output, @O_iErrorState int output, @oErrString varchar(255) outputasdeclare@VendorID char(30),@VDOCNMBR char(30),@szResult char(30),@SQL varchar(4000)Set @SQL = ''set nocount onset @oErrString = ''set @O_iErrorState = 0BeginDeclare C1 Cursor Forselect distinct max(a.DOCNUMBR)from dbo.PM10000 awhere a.VENDORID = @VendorIDand a.DOCNUMBR like ltrim(rtrim(@VDOCNMBR)) + '%'unionselect distinct max(a.DOCNUMBR)from dbo.PM20000 awhere a.VENDORID = @VendorIDand a.DOCNUMBR like ltrim(rtrim(@VDOCNMBR)) + '%'unionselect distinct max(a.DOCNUMBR)from dbo.PM30200 awhere a.VENDORID = @VendorIDand a.DOCNUMBR like ltrim(rtrim(@VDOCNMBR)) + '%'Open C1Fetch Next From C1 Into @szResultWhile @@Fetch_Status=0 beginIf @szResult is not Null Beginset @szResult = SUBSTRING(@szResult,8,4)set @szResult =cast ((abs(cast ( @szResult as integer)+1)) as char)--print @szResultSet @szResult ='000'+@szResult+1set @I_vDOCNUMBR =@VDOCNMBR+'-'+@szResultEndElseBeginSet @szResult =@VDOCNMBR+'-0001'set @I_vDOCNUMBR =@szResultEnd Fetch Next From C1 Into @szResult endClose C1Deallocate C1set nocount offendreturn 0GO 推荐答案 If @szResult is not Null Begin set @szResult = SUBSTRING(@szResult, 8, 4) set @szResult = cast ((abs(cast(@szResult as integer) + 1)) as char) --print @szResult Set @szResult ='000' + @szResult + 1 set @I_vDOCNUMBR = @VDOCNMBR + '-' + @szResultEndElseBegin Set @szResult = @VDOCNMBR + '-0001' set @I_vDOCNUMBR = @szResultEnd 对我来说，看起来你将值增加两次，一次在这里 To me it looks like you increment the value twice, once hereset @szResult = cast ((abs(cast(@szResult as integer) + 1)) as char) 然后再来这里 and then again hereSet @szResult ='000' + @szResult + 1 你也工作字符串而不是整数。 最好声明一个临时变量来执行操作。 Also you work in a string instead of an integer.Might be better to declare a temporary variable to do the operations on.declare @counter integer;set @szResult = SUBSTRING(@szResult, 8, 4);set @counter = cast(@szResult as integer);set @counter = @counter + 1;set @szResult = FORMAT(@counter, 'D4'); -- fills up with zeroes, so 1 -> 0001... -- The rest of your code 要将数值格式化为字符串，请参阅 FORMAT（Transact-SQL） ） [ ^ ] b $ b和 标准数字格式字符串 [ ^ ] 这篇关于如何在插入前增加varchar值的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！