PL/SQL Help

1. rao
Member
Join Date
Jan 2002
Posts
111

## PL/SQL Help

Hi All

I am trying to pull all the odd and even numbers from right to Left.

The Number is 00 1 0072053 60 3716796.

Below is the logic that i am trying to achieve.

1.From the right to left, assign the odd/even position to each digit. Start with odd position.
2.Sum all digits in odd position and multiply the result by 3.
3.Sum all digits in even position.
4.Get the total for the two steps above and divide the result by 10.
5.The check digit is the number which adds the remainder to 10.

can anyone pleasehelp me to achieve this. I need to Deliver this.

2. I might be getting too old for this... couldn't put together what I understood is the requirement and the described logic.

Would you mind in showing what the result is supposed to look like?

3. rao
Member
Join Date
Jan 2002
Posts
111
00 1 0072053 60 3716796

The result of the above number should be single digit number which i am printing.

For example 5

4. wow... still can't follow but, if the problem involves searching a string and doing math with the pieces you may want to research/use functions below

substr() ... will allow you to pick a piece of the string.
lenght() ... will tell you the total lenght of the string.
to_number() ... will convert a char into a number

5. I'm guessing that this is what you want. Although I got a check sum of 7.
I deserve at least half of the credit for helping you with your homework!

PAVB, I don't think that you are too old. Since I work at a university
I can take classes. So I end up in computer classes with people half
my age. I would say that they are too young.

Code:
SYS@devdb AS SYSDBA> DECLARE
2  	v_string  VARCHAR2(2000) :=  REPLACE('001 0072053 60 3716796', ' ', '');
3  	i	  PLS_INTEGER;
4  	digitSum  PLS_INTEGER := 0;
5  BEGIN
6  	i := 1;
7  	WHILE i < length(v_string)
8  	LOOP
9  	   digitSum := digitSum + substr(v_string, i, 1);
10  	   i := i + 2;
11  	END LOOP;
12
13  	digitSum := digitSum * 3;
14
15  	i := 0;
16  	WHILE i < length(v_string)
17  	LOOP
18  	   digitSum := digitSum + substr(v_string, i, 1);
19  	   i := i + 2;
20  	END LOOP;
21
22  	DBMS_OUTPUT.PUT_LINE('The check digit is: ' || MOD(digitSum, 10));
23  END;
24  /
The check digit is: 7

PL/SQL procedure successfully completed.

6. So it was a check-digit calculation! now I can see it LOL

Man, that was one of the best inventions of the 70's, second only to punching an "*" on column 80 -to signal some exception LMAO

7. Originally Posted by PAVB
So it was a check-digit calculation! now I can see it LOL

Man, that was one of the best inventions of the 70's, second only to punching an "*" on column 80 -to signal some exception LMAO
For my money adding a divide by zero fault checking would have been the best invention of the 70's. When I was in high school we had a mini computer with teletype terminals. A disgruntled student would occasionally initiate a divide by zero error, in order to crash the computer, then leave. The people who maintained the computer would spend the next hour trying to recover everything. I suppose it was funny the first few times he did it. Fortunately I was doing all my work on the Atari 800 with a basic cartridge and a cassette tape drive. Not that I am old or anything...

8. Originally Posted by gandolf989
Not that I am old or anything...
I know, I know... other people are too young; that's the problem

9. Senior Member
Join Date
Apr 2006
Posts
377
An option using SQL could be:

Code:
SQL> with t as (
2       select replace('00 1 0072053 60 3716796',' ') digits from dual)
3    select mod((odd+even),10) check_digit
4    from (select
5       sum(regexp_substr(digits, '.', 1, level*2-1))*3 odd,
6       sum(regexp_substr(digits, '.', 1, level*2)) even
7       from t
8       connect by level <= (length(replace(digits, ' '))+1)/2);

CHECK_DIGIT
-----------
5
Last edited by ebrian; 06-27-2009 at 04:06 PM.

10. rao
Member
Join Date
Jan 2002
Posts
111
EBRAIN,