Stored procedure and substring

Transact-SQL
1

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`
2

The easiest way to do it is to use a string splitter function. Copy and install @JeffModen's delimitedSplit8K from here. Then use it like this:

DECLARE @Description VARCHAR(256) = 'Smith Jr., John';
SELECT
	 -- this is the first 5 letters of last name 
	MAX(CASE WHEN ItemNumber = 1 THEN LEFT(REPLACE(Item,' ',''),5)END ) 
	+ 
	-- this is the first letter of the first name
	MAX(CASE WHEN ItemNumber = 2 THEN LEFT(REPLACE(Item,' ',''),1) ELSE '' END)
FROM
	dbo.delimitedSPlit8K(@Description,',');

You can write the query without using a splitter function, but it is ugly, painful, and unreadable. For example, I wrote the following, but I can't figure out what I did when I looked at it a second time :slightly_smiling:

DECLARE @Description VARCHAR(256) = 'Smith Jr., John';
-- Last name
SELECT LEFT(LEFT(REPLACE(@Description,' ',''),CHARINDEX(',',REPLACE(@Description,' ','')+',')),5) 
-- first letter of first name
SELECT LEFT(REVERSE(LEFT(REVERSE(REPLACE(@Description,' ','')), 
	CHARINDEX(',',REVERSE(REPLACE(@Description,' ',''))+',')-1 )),1)
3

You're not setting the @description value to any variable that I can see.

Personally I wouldn't use a splitter in this case as it's not necessary and CROSS APPLY allows the prep work to be done outside the main SELECT. To me, splitting actually complicates the logic (I think the code above has a bug and will return the middle name not the first name?). How about the code below instead? The first 3 columns are returned just to demo the results, for the final code you can remove everything except the last column in the SELECT.

SELECT     
    full_name,
    LEFT(full_name, last_name_length) AS last_name,
    first_name,
    LEFT(full_name, CASE WHEN last_name_length > 6 THEN 6 ELSE last_name_length END) + LEFT(first_name, 1) AS name_combined
FROM (
    --SELECT @description AS full_name
    SELECT 'Monkey' AS full_name UNION ALL
    SELECT 'Smith Sr., John' UNION ALL --<<--Change "Jr." to "Sr." so <> first letter of first name
    SELECT 'Williams III M.D. Esq., Franklin Scarborough Shelton'
) AS test_names
CROSS APPLY (
    SELECT CHARINDEX(' ', full_name) AS first_space,
        CHARINDEX(',', full_name) AS first_comma
) AS ca1
CROSS APPLY (
    SELECT CASE
        WHEN first_space < 2 AND first_comma < 2 THEN LEN(full_name)
        WHEN first_space < first_comma THEN first_space - 1
        ELSE first_comma - 1
        END AS last_name_length
) AS ca2
CROSS APPLY (
    SELECT CASE WHEN first_comma = 0 THEN '' 
        ELSE LTRIM(RTRIM(SUBSTRING(full_name, first_comma + 1, ISNULL(NULLIF(
            CHARINDEX(' ', SUBSTRING(full_name, first_comma + 2, 8000)), 0), 8000))))
        END AS first_name
) AS ca3
4

Thank you both for responding. Some of the info here is new to me so I'm going to work with what you've posted and post the results. I'm sure there's others who can benefit from a solution.

5

Go this one to know the technique how to restore the suspected database. http://sqltechtips.blogspot.in/2015/11/suspect-database-recovery-techniques.html