I have generated a sequence and referred to dual table to pass the next value in the function. Like select x_seq.nextval into v_seqvalue from dual (v_seqvalue varchar2(10)) inside the function.. I want to implement MOD10 function on v_seqvalue. MOD10 Logic says that every 2nd digit from right to left should get doubled(or multiply by 2) and the digit doubled if greater than 10, then it should be subtracted by 9 .Finally the sum of all the digits will be divided by 10. this mod I will be concatenating with theinput v_seqvalue and will return it. For Example.. 1234567890 becomes 2264165890 (9+9=18>10 so 18-9=9and so on..) How could I achieve this ? Thanks in advance...
User TO_CHAR to convert the number into a string then parse the string looking at every 2nd character. Take the character, convert it to a number with TO_NUMBER, double it, if >10 subtract 9, convert it back to a character with TO_CHAR and place it back in the original string. SQL isn't necessarily the best language to do this in so if this turns out to be a bottleneck then write an external procedure in C to handle the calculation.
