# using RIGHT MAX CHARINDEX to get value

### Question

• ```declare @lotTech varchar(64)
declare @lontNum varchar(64)SELECT @lotTech = RIGHT(MAX(l.lotID),CHARINDEX(' ','TECH'))
FROM  lots l
if (@lotTech = '')
SET @lotNum = 'TECH 1';
else
SET @lotNum = 'TECH' + SPACE(1) + @lotTech + 1;```
Can't get this to find if there is a null value or max value and 1 to concatnated data?

• Edited by Tuesday, May 06, 2014 6:18 PM
Tuesday, May 06, 2014 5:25 PM

• Try:

```if (@gradeDesc = 'TECH GRADE')
begin
SELECT @lotTech = MAX(REPLACE(l.lotID, 'TECH ',''))
FROM  lots l
WHERE LotID LIKE 'TECH [0-9]%'

if @lotTech IS NULL
SET @lotNum = 'TECH 1';
else
SET @lotNum = 'TECH ' + CONVERT(VARCHAR(10),CAST(@lotTech as INT) + 1);
end	```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

• Marked as answer by Tuesday, May 06, 2014 7:17 PM
Tuesday, May 06, 2014 6:41 PM

### All replies

• ```declare @lotTech varchar(64)

SELECT @lotTech = RIGHT(MAX(l.lotID),CHARINDEX(' ','TECH'))
FROM  lots l
if (@lotTech = '')
SET @lotNum = 'TECH 1';
else
SET @lotNum = 'TECH' + SPACE(1) + @lotTech + 1;```
Can't get this to find if there is a null value or max value and 1 to concatnated data?
forgot to declare lotnum variable? and what is lots.. post complet DDL.

Tuesday, May 06, 2014 5:28 PM
• Can you post some rows in lots table and desired output? What exactly are you looking for - finding the last number in that lotID column following the tech word?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Tuesday, May 06, 2014 5:30 PM
• ```@productID as bigint,
@lotNum as varchar(64),
@manufactureDate as datetime,
@expirationDate as datetime = null,
)
as
BEGIN

declare @lotcheck varchar(64)
declare @lotTech varchar(64)

SELECT @lotcheck = lotid FROM Lots WHERE lotid = @lotNum

if (@expirationDate is null)
begin
SELECT @expirationDate = dateadd(day, ch.expiration, @manufactureDate)
FROM products p
INNER JOIN chemicals ch on p.chemicalID = ch.chemicalID
WHERE p.productID = @productID
end

begin
SELECT @lotTech = RIGHT(MAX(l.lotID),CHARINDEX(' ','TECH'))
FROM  lots l
if (@lotTech = '')
SET @lotNum = 'TECH 1';
else
SET @lotNum = 'TECH' + SPACE(1) + @lotTech + 1;
end
if (@lotcheck is null)
begin
INSERT INTO Lots(lotID, chemicalID, expirationDate, manufactureDate, confirmed, productID)
SELECT @lotNum as lotID, p.chemicalID, @expirationDate as expirationDate, @manufactureDate as manufactureDate, 0 as confirmed, p.productID
FROM products p
INNER JOIN chemicals ch on p.chemicalID = ch.chemicalID
WHERE p.productID = @productID
end
else
begin
UPDATE Lots
SET manufactureDate = @manufactureDate,
expirationDate = @expirationDate,
productID = @productID
WHERE lotID = @lotNum
end
return
END```

I'm showing more of the TSQL which works fine but the part I'm not understanding is the use of essentially parsing the data that is TECH and the number which has to be the maximum number for TECH.

The output should be an ascending value concatnated with the word 'TECH' and a space.  Initially there is nothing in the lotNum column.  I need to get the MAX number out of the lots table for the paticular grade descripton -  'TECH GRADE' and then add 1 to that retrived number for the insert of the record.

So the first record to be inserted after the MAX number is found to be not present is

TECH 1

Following queries have to find the MAX number in the whole table and then add 1  and insert when 'TECH GRADE' description so

TECH 2

TECH 3

.

.

• Edited by Tuesday, May 06, 2014 6:38 PM
Tuesday, May 06, 2014 6:30 PM
• You can use a CASE expression instead of IF ELSE:

```SET @lotNum = CASE WHEN @lotTech = '' THEN 'TECH 1'
ELSE  CONCAT('TECH', SPACE(1), @lotTech + '1') END;```

Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Tuesday, May 06, 2014 6:39 PM
• Try:

```if (@gradeDesc = 'TECH GRADE')
begin
SELECT @lotTech = MAX(REPLACE(l.lotID, 'TECH ',''))
FROM  lots l
WHERE LotID LIKE 'TECH [0-9]%'

if @lotTech IS NULL
SET @lotNum = 'TECH 1';
else
SET @lotNum = 'TECH ' + CONVERT(VARCHAR(10),CAST(@lotTech as INT) + 1);
end	```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

• Marked as answer by Tuesday, May 06, 2014 7:17 PM
Tuesday, May 06, 2014 6:41 PM