Incrementing a combination of nvarchar and numbers

Question

following scenario: I need to increment a code like this one DS0000001 So next Item would be DS0000002, following DS0000003 and so on.

So for generating this code in a big INSERT Statement I wrote the following function:

CREATE FUNCTION dbo.eufn_e5_importCRM_getNextClientCode(
  @partnerCode AS NVARCHAR(10),
  @number AS INT
    )
    RETURNS NVARCHAR(20)
    AS
    BEGIN
        DECLARE @next AS INT
        DECLARE @zeroes AS NVARCHAR(10)
        DECLARE @nextStr AS NVARCHAR(10)
        DECLARE @return AS NVARCHAR(20)
        DECLARE @partnerPrefix AS NVARCHAR(10)

        SELECT @next = RIGHT(c.clientCode,7) FROM Clients c

        SELECT @partnerPrefix = partners.partnerPrefix
          FROM partners WHERE partnerCode = @partnerCode
        SET @next = ISNULL(@next,0) + @number   
        SET @nextStr = CONVERT(NVARCHAR(10),@next)

        SELECT @zeroes = CASE WHEN @next > 9 THEN '00000'
                WHEN @next > 99 THEN  '0000'
                WHEN @next > 999 THEN  '000'
                WHEN @next > 9999 THEN  '00'
                WHEN @next > 99999 THEN  '0'
                WHEN @next > 999999 THEN  ''
                ELSE '000000'
        END

        IF ISNULL(@next,'') = ''
        BEGIN
            SET @return = @partnerPrefix + @zeroes + '1'
        END
        ELSE SET @return = @partnerPrefix + @zeroes + @nextStr

        RETURN @return
    END

@number gets the row number I added in the insert statement manually with the ROW_NUMBER()-function.

This already works fine - it inserts the right code into the table.

What I don't like about the code is this part here:

SELECT @zeroes = CASE WHEN @next > 9 THEN '00000'
        WHEN @next > 99 THEN  '0000'
        WHEN @next > 999 THEN  '000'
        WHEN @next > 9999 THEN  '00'
        WHEN @next > 99999 THEN  '0'
        WHEN @next > 999999 THEN  ''
        ELSE '000000'
END

I already searched a while on stackoverflow and also googled, but I could not find a function like convert that leaves the "0" standing where they are.

Any ideas how to make this function more flexible and if possible: reusable?

Thanks in advance for any help!

Maybe you are right. Slightly different scenario though. Seeing the solution it sounds similar, but in your link the answer (and most answers) did use other functions. — Bruellhusten, Sep 02 '13 at 13:00

score 1 · Accepted Answer · answered Sep 02 '13 at 12:35

1

Here's an you can do it like this

Declare @a int
Set @a =8756
Select 'DS' + REPLACE(STR(@a, 7), SPACE(1), '0')

Demo: http://sqlfiddle.com/#!3/d41d8

answered Sep 02 '13 at 12:35

Satpal

132,252
13
159
168

Your sqlfiddle link seems broken. – André Laszlo Sep 02 '13 at 12:37
@AndréLaszlo, Yes working on it, even I am not familiar with SQL Fiddle. It working now – Satpal Sep 02 '13 at 12:39

Incrementing a combination of nvarchar and numbers

1 Answers1