描述:数据库数据有cy001,cy999,wx123…
通过cy模糊查询、且cy后面数字最大的数据,然后加 1
SELECT
CONCAT(
'cy',
CASE
WHEN LENGTH( CAST( SUBSTRING( MAX( CODE ), 3 ) AS UNSIGNED ) + 1 ) > LENGTH(
SUBSTRING( MAX( CODE ), 3 )) THEN
CAST( SUBSTRING( MAX( CODE ), 3 ) AS UNSIGNED ) + 1 ELSE LPAD(
CAST( SUBSTRING( MAX( CODE ), 3 ) AS UNSIGNED ) + 1,
LENGTH(
SUBSTRING( MAX( CODE ), 3 )),
'0'
)
END
) AS new_code
FROM
your_table
WHERE
CODE LIKE 'cy%'