用户定义函数中的字母数字值增加

问题描述

我正在尝试在SQL Server 2005下编码用户定义的函数，该函数会将字母数字值的整数部分加1.例如，uf_AlphanumericIncrease ('A000299')应该返回'A000300'.这是我到目前为止所做的；

I'm trying to code a user defined function under SQL Server 2005 that will increase integer part of alphanumeric value by one. For example, uf_AlphanumericIncrease ('A000299') should return 'A000300'. Here's what I've done so far;

ALTER FUNCTION uf_AlphaNumericIncrement
(
@ID varchar(10)
)
RETURNS VARCHAR(10) AS
BEGIN
    DECLARE @RES varchar(10);
    IF SUBSTRING(@ID,LEN(@ID),1)='9'
        SET @RES=SUBSTRING(@ID,1,LEN(@ID)-2)+CAST (CAST(SUBSTRING(@ID,LEN(@ID)-1,1) AS smallint)+1 AS VARCHAR(10))+'0';
    ELSE
        SET @RES=SUBSTRING(@ID,1,LEN(@ID)-1)+CAST (CAST(SUBSTRING(@ID,LEN(@ID),1) AS smallint)+1 AS VARCHAR(10));
    RETURN @RES;
END

但是如您所见，它仅适用于最后一位数字.我需要将其置于循环下，以便它可用于A002999等.有什么想法吗?

But as you can see it only works for last digit. I need to get it under loop so it can work for A002999 and so on. Any ideas?

给定值的Alpha前缀长度不能超过一个字符，或者根本没有.

Given value might have alpha prefix longer than one character, or none at all.

推荐答案

现在可以使用任何长度的前缀和数字部分(每个最多20个)

Now works with any length of prefix and number part (well upto 20 each)

DECLARE @prefix varchar(20), @numberstr varchar(20), @number int, @Val varchar(40)

SELECT @Val = 'ABCD000006'
--SELECT @Val = 'A03'

SELECT @prefix = LEFT(@Val, PATINDEX ('%[0-9]%', @Val) -1)
SELECT @numberstr = SUBSTRING(@Val, PATINDEX ('%[0-9]%', @Val), 8000)
SELECT @number = CAST(@numberstr AS int) + 1
SELECT @prefix + RIGHT(REPLACE(SPACE(LEN(@numberstr)), ' ', '0') + CAST(@number AS varchar(20)), LEN(@numberstr))

这篇关于用户定义函数中的字母数字值增加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！