AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Thursday, February 04, 2010

TSQL: increase series number

Here a function to increase series number, I used it in inventory report to show currently available series. If you have series like ABC1001, and 5 items were sold - you can use this function to get next available series, e.g. ABC1006. Based on a GetNumbers() function from  Extracting numbers with SQL Server  article.

CREATE FUNCTION dbo.fSeriesAddQty(@DATA VARCHAR(8000), @intQty int)
RETURNS VARCHAR(8000)
AS
BEGIN  
    declare @Pos1 int, @Pos2 int
    declare @Result VARCHAR(8000)
    select @Pos1=0, @Pos2=0
    set @Pos1=PATINDEX('%[0-9.-]%', @DATA)
    set @Pos2=PATINDEX('%[-]%', @DATA)
    if @Pos2>@Pos1
        set @Pos1=@Pos2+1
    set @Pos2=PATINDEX('%[^0-9.-]%', SUBSTRING(@DATA, @Pos1, 8000) + 'X')
    if @Pos1>0 AND @Pos2 >0
        begin
        set @Result=SUBSTRING(@DATA, @Pos1, @Pos2-1)
        if isnumeric(@Result)=1
            set @Result=Left(@DATA,@pos1-1) + cast(cast(@Result as int) + @intQty as varchar(100)) + SUBSTRING(@DATA,@Pos2+@Pos1-1, 8000)
        else
            set @Result=@DATA
        end
    else
        set @Result=@DATA

    return @Result
END

Labels: ,

1 Comments:

Blogger DataPlus - Custom Data Services said...

That's quite a function and I'm not sure that I will ever use it but thanks for posting it. Also thanks for reminding me that I must delve into SQL server.

3:35 PM  

Post a Comment

Links to this post:

Create a Link

<< Home