CODEWITHSUNDEEP
sqlsql-server
k80sg
k80sg

Reputation: 2473

SQL Server + String format follow by an incrementing number solution

I have a voucher format stored which reads something like this:

 [VOUTYPECODE][ISBIRTHDAY][ISREUSABLE][STARTD][ENDD]VT555 + (RunningNo)

Referring to the query portion below, @VouFormatLastNum is a string format represented like this:

'VouT001012012010420120704VT555181' derived by querying the respective [] bracketed items from the voucher format as seen above. Now, I am using 'VT555' as a blocker which is stored in @VouFormatCore (derived by detaching all bracketed items) to retrieve the running no. of the previous issued voucher which is '181' so that I can add a +1 to that running no. for my next voucher.

Everything works fine until I change my voucher format's with a different blocker other than VT555. Apparently, the last voucher will still have VT555 but the new @VouFormatCore has been changed to some other value hence I can no longer perform the query below to retrieve the running no.

I have been trying to think of ways to get by this problem. Anyway care to share a solution? Thanks.

--@VouFormatLastNum = 'VouT001012012010420120704VT555181' (This is obtained by querying the Top 1 voucher from the voucher table ordered by issue date.)

    Declare @position as int    
    Set @position = (select len(@VouFormatLastNum) - charindex(reverse(@VouFormatCore),reverse(@VouFormatLastNum)) +1)  

--@VouFormatLastNum will now contain the next running no. after selecting the substring below:
    Set @VouFormatLastNum = (select substring(@VouFormatLastNum, @position+1,len(@VouFormatLastNum)-@position) + 1)
    select @VouFormatLastNum
            Set @NextVoucher= @var1 + @var2 + @var3 + @var4 + @var5 + @VouFormatCore + @VouFormatLastNum

Upvotes: 2

Views: 275

Answers (1)

MatBailie
MatBailie

Reputation: 86745

Are you able to change the storage mechanism?

As you have 6 or 7 pieces of information, you should ideally have 6 or 7 fields in your storage table. The final string can then be reconstituted from those fields, whilst also making querying individual elements very simple...

I'll assume MS SQL Server from your example...

CREATE TABLE vouchers (
  id                INT IDENTITY(1,1),
  VouTypeCode       NCHAR(7)      NOT NULL,
  IsBirthday        NCHAR(2)      NOT NULL,
  IsReusable        NCHAR(2)      NOT NULL,
  StartD            SMALLDATETIME NOT NULL,
  EndD              SMALLDATETIME NOT NULL,
  PRIMARY KEY (id)
)

Now, to create a new voucher, just insert into the table, except for the id field, and a new id will be created for you. And you can create the whole voucher code from that record.

You can also, as necessary, change the data types to e more flexible/appropriate to their actual use. And add additional fields, such as a 'Blocker' field to allow different vouchers to have different blockers.


If you can't change the data structure, you need to be specific about the constraints on the data-format of the voucher codes. For example, I see you use REVERSE(), so can I assume that 'VT555' may sometimes appear earlier in the string? Will the running number always be a set length, or have a max/min length?

Without knowing the exact constraints it's not possible to write an algorithm that deals with multiple different blockers.

Options could be...

Check for other blockers if 'VT555' never appears CHARINDEX() = 0

Check for other blockers if CHARINDEX() > 8 (or some other value), as it appears to early in the string to count.

Scan the reversed string for the first non-numeric character. The alpha and numeric characters surrounding that point form the blocker. Iterate though all blockers until an appropriate match is found.

To be more specific in the answer, however, you need to be more specific about the problem space. Sorry.

Upvotes: 2

Related Questions