CODEWITHSUNDEEP
phpmysql
aozora
aozora

Reputation: 421

Auto increment a varchar in sql query

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.

Upvotes: 6

Views: 1023

Answers (3)

CD001
CD001

Reputation: 8482

I'm assuming that your barcode here is essentially INT(7) UNSIGNED ZEROFILL prefixed with a VARCHAR.

To keep this as an SQL only solution as preferred I just created a MySQL function. The trick is to treat the numeric part of the barcode as an unsigned, zero-filled integer - this keeps your leading zeroes in tact.

CREATE FUNCTION `barcode_increment`(`sin_barcode` VARCHAR(12), `sin_prefix` VARCHAR(5)) RETURNS varchar(12)
    READS SQL DATA
    DETERMINISTIC
    COMMENT 'increments a barcode that has a prefix'
BEGIN   
    DECLARE i_barcode_num INT(7) UNSIGNED ZEROFILL;
    SET i_barcode_num = CAST(REPLACE(sin_barcode, sin_prefix, '') AS UNSIGNED);
    SET i_barcode_num = i_barcode_num + 1;

    RETURN CONCAT(sin_prefix, i_barcode_num);
END

You can then call this function through a standard SQL query like so:

SELECT barcode_increment('PU0000012', 'PU') AS new_barcode

Which will give you PU0000013

Note: If PU is always the prefix, you could just declare and set that in the function itself - removing the need to pass it in as a parameter.

Alternatively you can do it in a single (slightly messy) query which relies on LPAD rather than ZEROFILL:

SELECT CONCAT('PU', LPAD(CAST(REPLACE('PU0001234', 'PU', '') AS UNSIGNED) + 1, 7, 0))

That example would give you PU0001235

Upvotes: 1

zanderwar
zanderwar

Reputation: 3730

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

Upvotes: 3

Zafar Malik
Zafar Malik

Reputation: 6854

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';

Upvotes: 1

Related Questions