在SQL查询中自动递增varchar [英] Auto increment a varchar in sql query
问题描述
我正在尝试从表中自动增加varchar列.它以"PU"为前缀.我用它来抓取最后一个数字并将其递增一个.
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';
最后一个条形码是PU0000012.它返回一个PU13.它将删除0.
The last barcode is PU0000012. It returns a PU13. It removes the 0.
所以我尝试将其替换为:
So I tried replacing it with:
LEFT( BARCODE, 7 )
它返回了正确的PU0000013.但是,假设我将PU1234567作为最后一个条目.它返回:PU000001234568.
It returned PU0000013 which is correct. But suppose I put a PU1234567 as last entry. It returns: PU000001234568.
有什么建议吗?我正在使用php btw.如果可以选择使用php,我可以接受.但是我更愿意在可能的情况下在sql查询中解决该问题.
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.
推荐答案
尝试一下:
<?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
返回:PU000001234568
Returns: PU000001234568
这篇关于在SQL查询中自动递增varchar的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!