Geek Answers Handbook

Auto increase varchar in sql query

I am trying to automatically increase a varchar column from a table. This is the prefix "PU". I use this to capture the last number and increase it by one.

I tried this request 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 final barcode is PU0000012. It returns PU13. It removes 0.

So, I tried replacing it:

 LEFT( BARCODE, 7 )

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

Any suggestions? I am using php btw. If you want to use php, I am open to it. But I prefer that it be resolved in the sql query, if possible.

+6
source share
3 answers

Try the following:

 <?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

+2
source

try to execute the request -

 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';
+1
source

I assume that your barcode here is basically INT(7) UNSIGNED ZEROFILL with the prefix VARCHAR .

To save this as a SQL-only solution, I simply 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 zeros 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 call this function through a standard SQL query, for example:

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

What will give you PU0000013

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

Alternatively, you can do this in one (slightly dirty) query that relies on LPAD and not ZEROFILL :

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

This example will give you PU0001235

0
source

More articles:


All Articles