-
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.
-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
00 1 0072053 60 3716796
The result of the above number should be single digit number which i am printing.
For example 5
-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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...
-
Originally Posted by gandolf989
Not that I am old or anything...
I know, I know... other people are too young; that's the problem
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
EBRAIN,
Can ou please help me in putting the SQL into PL/SQL...It will be a great help
Regards
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|