Thanks for the question, rama.

Asked: February 05, 2016 - 4:00 pm UTC

Last updated: February 14, 2016 - 4:07 am UTC

Version: 11g

Viewed 1000+ times

You Asked

write a pl/sql function accept date of birth as "dd-mm-yyyy" and sum all digits till you get single digit number to show as he lucky number.

and Connor said...

Something like this ?

SQL> set serverout on
SQL> declare
  2    l_input varchar2(20) := '31/01/1978';
  3    l_output int;
  4  begin
  5    loop
  6      dbms_output.put_line('------------------');
  7      dbms_output.put_line('l_input='||l_input);
  8      l_output := 0;
  9      for i in 1 .. length(l_input)
 10      loop
 11        if substr(l_input,i,1) between '0' and '9' then
 12           l_output := l_output + to_number(substr(l_input,i,1));
 13        end if;
 14      end loop;
 15      dbms_output.put_line('l_output='||l_output);
 16      exit when l_output < 10;
 17      l_input := to_char(l_output);
 18    end loop;
 19    dbms_output.put_line('------------------');
 20    dbms_output.put_line('Lucky='||l_output);
 21  end;
 22  /
------------------
l_input=31/01/1978
l_output=30
------------------
l_input=30
l_output=3
------------------
Lucky=3

PL/SQL procedure successfully completed.

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

function for luck number

rama raju, February 08, 2016 - 4:54 am UTC

i want to create function accept the dd-mm-yyyy format as parameter and sum all the digits untill get the single digit.

i tried like this

create or replace function luck_no(vdob varchar2)
return number
is
vnum number;
begin
select substr(substr(to_char(to_date('vdob','dd-mm-yyyy'),'dd'),1,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'dd'),2,1)
+substr(to_char(to_date('vdob','dd-mm-yyyy'),'mm'),1,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'mm'),2,1)
+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),1,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),2,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),3,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),4,1),1,1)
+substr(substr(to_char(to_date('vdob','dd-mm-yyyy'),'dd'),1,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'dd'),2,1)
+substr(to_char(to_date('vdob','dd-mm-yyyy'),'mm'),1,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'mm'),2,1)
+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),1,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),2,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),3,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),4,1),2,1) into vnum from dual;
return vnum;
end;

function is created,
but not excuted.
when i executed it with select statement error was came...
error
nonnumeric is found when numeric expected

thank u...

February 08, 2016 - 7:58 am UTC

But why do you have all of this ?

If its a varchar2 you are passing in, and you want each digit - why all the effort of converting to dates ?

Is there something in the requirement you are not saying ?

Send us a test case, and the error you are getting. We need to see YOUR output - we can't just guess at it

model clause ?

Rajeshwaran Jeyabal, February 08, 2016 - 8:33 am UTC

Is that this can be done using model clause? I am unable to proceed with repeated iteration, is that something can be achievable?

rajesh@ORA10G> select total
  2  from (
  3  select *
  4  from dual
  5  model
  6     dimension by (1 x)
  7     measures( to_number(to_char(to_date('31/01/1978','dd/mm/yyyy'),'yyyymmdd')) dt,0 x2,0 total)
  8     rules iterate(1000) until ( x2[iteration_number] is null )
  9     ( x2[iteration_number] = substr(dt[1],iteration_number+1,1) ,
 10      total[1] = total[1] + nvl(x2[iteration_number],0) )
 11       )
 12  where x = 1
 13  /

     TOTAL
----------
        30

1 row selected.

rajesh@ORA10G>

February 10, 2016 - 5:21 am UTC

One way of course would be just use MODEL as a means of iterating, and still having a function to sum the digits, eg

SQL> create or replace
  2  function digit_sum(x varchar2) return int is
  3    y int := 0;
  4  begin
  5    for i in 1 .. length(x) loop
  6      y := y  + to_number(substr(x,i,1));
  7    end loop;
  8    return y;
  9  end;
 10  /

Function created.

SQL>
SQL> select total
  2  from (
  3  select *
  4  from dual
  5  model
  6  dimension by (1 x)
  7  measures(
  8    '31011978' str,
  9    ' ' str2,
 10    1000 total
 11    )
 12  rules iterate(1000) until ( total[1]<10 )
 13     ( total[1] = digit_sum(str[1])   ,
 14       str[1] = to_char(total[1]) )
 15  )
 16  /

     TOTAL
----------
         3

An alternative (and I'm sure there are better ways than mine) would be iterate continuously and keep track of mapping iteration number to the correct substr position in the string as we go

SQL> select total lucky_number
  2  from (
  3  select *
  4  from dual
  5  model
  6  dimension by (0 x)
  7  measures(
  8    '31011978' str,
  9    0 this_tot,
 10    0 idx,
 11    1000 total,
 12    0 iter_base
 13    )
 14  rules iterate(1000) until ( total[0]<10 )
 15     ( idx[0] = mod(iteration_number+1-iter_base[0],1+length(str[0]))
 16      ,this_tot[0] = nvl(this_tot[0],0) + to_number(substr(str[0],idx[0],1))
 17      ,total[0] = case when idx[0] =  length(str[0]) then this_tot[0] end
 18      ,this_tot[0] = case when idx[0] =  length(str[0]) then 0 else this_tot[0] end
 19      ,iter_base[0] = case when idx[0] =  length(str[0]) then iteration_number+1 else iter_base[0] end
 20      ,str[0] = case when idx[0] =  length(str[0]) then to_char(total[0]) else str[0] end
 21  )
 22  )
 23  /

LUCKY_NUMBER
------------
           3

1 row selected.

How about a pure sql solution

Tom, February 08, 2016 - 6:22 pm UTC

You could also do this in pure sql.

with qry as (select '01/01/1970' d from dual)
select sum(substr(d, level, 1))
from qry
where substr(d, level, 1) between '0' and '9'
connect by level <= length(d)

If you wanted to check the format first, then create a function where you do a to_date (or however you validate the format) then the select.

February 09, 2016 - 1:17 am UTC

I'm not sure that's the intent - I think the aim is:

1) sum up the digits, 1+1+1+9+7 = 19
2) is the result larger than 10 ?
yes - goto 1
no - we're done

So in this case, it would be:

19
10
1

(I think that's the requirement)

How about this ?

BC, February 10, 2016 - 6:04 pm UTC

Connor,

How about this ?

First we figure out if the sum of all digits in the Date Of Birth > 10, if it is then you take all digits from that sum and add them to each other. If not then you just add the digits of the supplied Date of Birth.

select distinct sum( substr( new_dob, level, 1 ) ) over()
from ( select distinct case
when sum( substr( dob, level, 1 ) ) over() > 10 then
to_char( sum( substr( dob, level, 1 ) ) over() )
else
dob
end new_dob
from ( select replace( '12-31-4712', '-', '' ) dob
from dual )
connect by level <= 8 )
connect by level <= length( new_dob )

I made a couple of assumptions here, Date of Birth will always be a valid date and will have a '-' separator.

I tested it with several values including 12-31-4712, a future birth day.

Thanks
BC

February 11, 2016 - 3:09 am UTC

Close but some occasional dates need more than two iterations ...

SQL> select distinct sum( substr( new_dob, level, 1 ) ) over()
  2  from ( select distinct case
  3  when sum( substr( dob, level, 1 ) ) over() > 10 then
  4  to_char( sum( substr( dob, level, 1 ) ) over() )
  5  else
  6  dob
  7  end new_dob
  8  from ( select replace( '29091988', '-', '' ) dob
  9  from dual )
 10  connect by level <= 8 )
 11  connect by level <= length( new_dob ) ;

SUM(SUBSTR(NEW_DOB,LEVEL,1))OVER()
----------------------------------
                                10

But I can find none that require 4...

SQL> set serverout on
SQL> begin
  2  for i in -50000 .. 50000 loop
  3
  4  declare
  5    l_input varchar2(20) := to_char(sysdate+i,'ddmmyyyy');
  6    l_output int;
  7    iter int;
  8  begin
  9    iter := 0;
 10    loop
 11      iter := iter + 1;
 12      l_output := 0;
 13      for i in 1 .. length(l_input)
 14      loop
 15           l_output := l_output + to_number(substr(l_input,i,1));
 16      end loop;
 17      exit when l_output < 10;
 18      l_input := to_char(l_output);
 19    end loop;
 20    if iter >= 4 then
 21       dbms_output.put_line(to_char(sysdate+i,'ddmmyyyy')||', iterations '||iter);
 22    end if;
 23  end;
 24
 25
 26  end loop;
 27  end;
 28  /

PL/SQL procedure successfully completed.

so maybe one more level of nesting and we'd be done

function for lucky number

rama raju, February 11, 2016 - 11:44 am UTC

SQL> create or replace function luck_no(vdob varchar2)
2 return number
3 is
4 vnum number;
5 begin
6 select substr(substr(to_char(to_date('vdob','dd-mm-yyyy'),'dd'),1,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'dd'),2,1)
7 +substr(to_char(to_date('vdob','dd-mm-yyyy'),'mm'),1,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'mm'),2,1)
8 +substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),1,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),2,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),3,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),4,1),1,1)
9 +substr(substr(to_char(to_date('vdob','dd-mm-yyyy'),'dd'),1,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'dd'),2,1)
10 +substr(to_char(to_date('vdob','dd-mm-yyyy'),'mm'),1,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'mm'),2,1)
11 +substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),1,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),2,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),3,1)+substr(to_char(to_date('vdob','dd-mm-yyyy'),'yyyy'),4,1),2,1) into vnum from dual;
12 return vnum;
13 end;
14 /

Function created.

SQL> select luck_no('28-02-1992') from dual
2 /
select luck_no('28-02-1992') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SCOTT.LUCK_NO", line 6

February 11, 2016 - 12:10 pm UTC

You have vdob in quotes, so its being treated as the STRING v-d-o-b not the parameter.

rama raju, February 13, 2016 - 8:52 am UTC

thank you very much for your support...I got it...

SQL> create or replace function luck_no(p_date date)
2 return number
3 as
4 v1 number;
5 v2 number;
6 v3 number;
7 r1 number;
8 r2 number;
9 begin
10 v1:=to_char(p_date,'DD');
11 v2:=to_char(p_date,'MM');
12 v3:=to_char(p_date,'YYYY');
13 r1:=nvl(substr(v1,1,1),0)+nvl(substr(v1,2,1),0)+nvl(substr(v2,1,1),0)+nvl(substr(v2,2,1),0)+substr(v3,1,1)+ substr(v3,2,1)+ substr(v3,3,1)+ substr(v3,4,1);
14 r2:=substr(r1,1,1)+substr(r1,2,1);
15 return r2;
16 end luck_no;
17 /

Function created.

SQL> SELECT LUCK_NO('28-FEB-1992') FROM DUAL
2 /

LUCK_NO('28-FEB-1992')
----------------------
6

SQL> SELECT LUCK_NO('2-DEC-1992') FROM DUAL
2 /

LUCK_NO('2-DEC-1992')
---------------------
8

February 14, 2016 - 4:07 am UTC


SQL>  set serverout on
SQL>  exec dbms_output.put_line(luck_no(date '1988-09-29'))
10

PL/SQL procedure successfully completed.

Is that what you intended ?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer