Auto increment a varchar in sql query

Question

I am trying to auto increment a varchar column from a table. It is prefix with "PU". I use this to grab the last number and increment it by one.

I tried this query below:

SELECT 
CONCAT(
LEFT( BARCODE, 2 ) 
, 
MAX( 
RIGHT( BARCODE, LENGTH(BARCODE)-2 ) 
* 1 )
+1 
) 
as newbarcode FROM KGU WHERE HW_TYPE='STANDARD PURGE UNIT';

The last barcode is PU0000012. It returns a PU13. It removes the 0.

So I tried replacing it with:

LEFT( BARCODE, 7 ) 

It returned PU0000013 which is correct. But suppose I put a PU1234567 as last entry. It returns: PU000001234568.

Any suggestions? I am using php btw. If an option is to use php I am open to it. But I prefer it to be solve in sql query if possible.

Answer 1

Try this:

<?php
// fetch the very last entry

$Barcode = 'PU000001234567';

preg_match("/(\D+)(\d+)/", $Barcode, $Matches); // Matches the PU and number

$ProductCode = $Matches[1];

$NewID = intval($Matches[2]);
$NewID++;


$BarcodeLength = 12;
$CurrentLength = strlen($NewID);
$MissingZeros = $BarcodeLength - $CurrentLength;

for ($i=0; $i<$MissingZeros; $i++) $NewID = "0" . $NewID;

$Result = $ProductCode . $NewID;

echo $Result;

// insert into database with $Result

Returns: PU000001234568

Answer 2

try below query-

SELECT CONCAT(LEFT(BARCODE, 2),LPAD(@n := @n + 1,7,0)) AS newbarcode 
FROM KGU AS a
JOIN (SELECT @n := 13) AS m
WHERE HW_TYPE='STANDARD PURGE UNIT';