I've been looking for several days and have been unable to find a solution. Some of the fields in this code are tied to another SP where they are set.
I have a stored procedure that imports values into my database. One of those fields needs to be changed during the upload process. In order to get the correct upload value I need to concatenate two substrings from the @Description variable. On import, @Description looks like this: (Lastname, Firstname) or (Lastname Jr, Firstname).
What I want to do is to pull the first six characters of Lastname then the first letter of Firstname. If the last name isn't six characters then the code should pull the last name until the comma. If there is a JR, then the space should be removed and the code will still pull the first six characters. This is what I'd like to have as the end result: (LastnaF).
During testing, when I set the variable @desFirst as a word ("Monkey"), the SP works correctly but something is wrong with the way I've written the substring codes. I've tried many different ways to get substrings from @Description but I can't get it right.
Is there a better way to create my upload field? If not, how can I create two substrings of @Description and concatenate?
`ALTER proc [dbo].[bspIMLinkComdataInvoice] (@Company bCompany, @ImportId varchar(20), @ImportTemplate varchar(20), @Form varchar(20), @msg varchar(120) output) --works with another stored procedure, set in other SP as set nocount on declare @rcode int, @recode int, @desc varchar(120), @tablename varchar(10) --works with another stored procedure, set in other SP declare @Ref varchar(15), @Description bDesc, @desLast varchar(6), @desFirst varchar(1) set @desFirst = substring(@Description, Charindex(',', @Description)+1,1) --want to pull first initial from first name which follows a space and comma set @desLast = (SELECT SUBSTRING(@Description, 1, CASE WHEN CHARINDEX(',', @Description) <= 6 THEN CHARINDEX(',', @Description)-1 ELSE substring(@Description, 1, 6) END)) --want to either pull the first 6 characters of the last name or the last name until the comma; if there's a JR, the space should be removed Set @Ref = (@desFirst+@desLast) --want to concatenate the @desFirst and the @desLast variables Select @rcode = 0, @msg = '' if @Form = 'APEntry' begin exec @rcode = dbo.bspIMBidtekDefaultsAPHB @Company, @ImportId, @ImportTemplate, @Form, @Ref, @msg output Update IMWE set UploadVal = @Ref where ImportTemplate=@ImportTemplate and ImportId=@ImportId and RecordSeq = 40 and RecordType = 'APHB' end bspexit: select @msg = isnull(@desc,'AP Invoice') + char(13) + char(10) + '[bspIMLinkComdataInvoice]' return @rcode`