Home » SQL & PL/SQL » SQL & PL/SQL » function for checking if it is a four digit number (oracle 10g)
function for checking if it is a four digit number [message #563615] Wed, 15 August 2012 22:58 Go to next message
anilkln
Messages: 7
Registered: August 2012
Junior Member
Hi


i nee a function which accepts 4 digit number and in four digit number the number should not be repeated. i want all the number in the output.

ex:1234
2367
1262(is not valid)
Re: function for checking if it is a four digit number [message #563616 is a reply to message #563615] Wed, 15 August 2012 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 22489
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: function for checking if it is a four digit number [message #563620 is a reply to message #563615] Thu, 16 August 2012 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
Note that we don't do homework for people but we help them, post what you tried and where you are stuck.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: function for checking if it is a four digit number [message #563621 is a reply to message #563616] Thu, 16 August 2012 00:57 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
You Can try Something like this,

create or replace function test_num(v_ip in number) return varchar2 is
v_text varchar2(18);
v_num1 number;
v_num2 number;
v_num3 number;
v_num4 number;
Begin
v_num1:=substr(v_ip,1,1);
v_num2:=substr(v_ip,2,1);
v_num3:=substr(v_ip,3,1);
v_num4:=substr(v_ip,4,1);
If v_num1=v_num2 or v_num1=v_num3 or v_num1=v_num4 or v_num2 =v_num3 or v_num2=v_num4 or v_num3=v_num4 then
v_text :='Invalid Number';
else
v_text :='Valid Number';
end if;
return v_text;
end;
/

SQL> sho err
No errors.
SQL> select test_num(1223) from dual;

TEST_NUM(1223)
--------------------------------------------------------------------------------
Invalid Number
Re: function for checking if it is a four digit number [message #563623 is a reply to message #563621] Thu, 16 August 2012 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not correct:
SQL> select test_num(12341) from dual;
TEST_NUM(12341)
---------------------------------------
Valid Number


Please format your code which means indent and not just use code tags.

Regards
Michel

[Updated on: Thu, 16 August 2012 01:26]

Report message to a moderator

Re: function for checking if it is a four digit number [message #563625 is a reply to message #563623] Thu, 16 August 2012 01:31 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Michel,

It was just an example(It works fine for 4,3,2 digit numbers: 11 is invalid because the number 1 is repeated). Also i have mentioned to try Something like this.

regards,
Veeresh
Re: function for checking if it is a four digit number [message #563627 is a reply to message #563625] Thu, 16 August 2012 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So better ask OP what are the actual requirements and limits of the code and so on rather than posting a code that may be irrelevant.
Or assume the range of numbers is not limited and post a code for this.

Regards
Michel
Re: function for checking if it is a four digit number [message #563630 is a reply to message #563627] Thu, 16 August 2012 02:14 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Michel,

I don't think the code i have posted is irrelevant. It gives an idea to start with.
I am sorry i have not used proper indentation in the code.

Regards,
Veeresh
Re: function for checking if it is a four digit number [message #563631 is a reply to message #563615] Thu, 16 August 2012 02:22 Go to previous messageGo to next message
John Watson
Messages: 4376
Registered: January 2010
Location: Global Village
Senior Member
Your function will need to do several tests. For example, would -1.234 be acceptable, or does the number have to be a positive four digit integer, which is what Veeresh's code is testing?
Re: function for checking if it is a four digit number [message #563633 is a reply to message #563631] Thu, 16 August 2012 02:37 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
I completely agree John,

What I wrote was just a code, that checks the number, I neither validated nor tested the input number.

Anyways,

I am very very sorry, If you think the code i have posted here is misguiding or irrelevant.

Regards,
Veeresh
Re: function for checking if it is a four digit number [message #563636 is a reply to message #563630] Thu, 16 August 2012 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any number it could be:
SQL> create or replace function test_num (p in number) return varchar2 is
  2    type numbers is table of number(1) index by binary_integer;
  3    tab  numbers;
  4    val  number := 
  5      abs(p)*power(10,nvl(length(substr(to_char(p),nullif(instr(to_char(p),'.'),0)))-1,0));
  6  begin
  7    loop
  8      case 
  9        when val is null then return null;
 10        when val < 10 then 
 11          if tab.exists(val) then return 'Invalid number';
 12          else return 'Valid number';
 13          end if;
 14        else 
 15          if tab.exists(mod(val,10)) then return 'Invalid number';
 16          else 
 17            tab(mod(val,10)) := 1; 
 18            val := trunc(val/10);
 19          end if;
 20      end case;
 21    end loop;
 22  end;
 23  /

Function created.

SQL> col t format a20
SQL> with data as (
  2    select null val from dual
  3    union all
  4    select 0 val from dual
  5    union all
  6    select 10 val from dual
  7    union all
  8    select 11 val from dual
  9    union all
 10    select -1234.5 val from dual
 11    union all
 12    select 12.341 val from dual
 13    )
 14  select val, test_num(val) t from data
 15  /
       VAL T
---------- --------------------

         0 Valid number
        10 Valid number
        11 Invalid number
   -1234.5 Valid number
    12.341 Invalid number

Regards
Michel
Re: function for checking if it is a four digit number [message #563659 is a reply to message #563636] Thu, 16 August 2012 06:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1950
Registered: January 2010
Senior Member
Michel Cadot wrote on Thu, 16 August 2012 04:06
For any number it could be:


Doesn't look that way:

SQL> select test_num(12345.6789) from dual;

TEST_NUM(12345.6789)
---------------------------------------------------
Valid number

SQL> 


Something like:

create or replace
  function test_num(
                    p in number
                   )
    return varchar2
      is
          tab sys.OdciNumberList;
      begin
          case
            when abs(p) < 1234 then return 'Invalid number';
            when abs(p) > 9876 then return 'Invalid number';
            when p != trunc(p) then return 'Invalid number';
          end case;
          tab(substr(p,1,1)) := 1;
          tab(substr(p,2,1)) := 1;
          tab(substr(p,3,1)) := 1;
          tab(substr(p,4,1)) := 1;
          if tab.count != 4 then return 'Invalid number'; end if;
          return 'Valid number';
end;
/


SY.

Re: function for checking if it is a four digit number [message #563661 is a reply to message #563659] Thu, 16 August 2012 07:08 Go to previous messageGo to next message
John Watson
Messages: 4376
Registered: January 2010
Location: Global Village
Senior Member
Close, but no cigar!
orcl> create or replace
  2    function test_num(
  3                      p in number
  4                     )
  5      return varchar2
  6        is
  7            tab sys.OdciNumberList;
  8        begin
  9            case
 10              when abs(p) < 1234 then return 'Invalid number';
 11              when abs(p) > 9876 then return 'Invalid number';
 12              when p != trunc(p) then return 'Invalid number';
 13            end case;
 14            tab(substr(p,1,1)) := 1;
 15            tab(substr(p,2,1)) := 1;
 16            tab(substr(p,3,1)) := 1;
 17            tab(substr(p,4,1)) := 1;
 18            if tab.count != 4 then return 'Invalid number'; end if;
 19            return 'Valid number';
 20  end;
 21  /

Function created.

orcl> select test_num(1023) from dual;

TEST_NUM(1023)
----------------------------------------------------------------------

Invalid number

orcl>


--
orcl> select test_num(1023) from dual;

TEST_NUM(1023)
------------------------------------------------------------------------

Invalid number

orcl> select test_num(2023) from dual;
select test_num(2023) from dual
       *
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at "SCOTT.TEST_NUM", line 8


orcl>

Confused

[Updated on: Thu, 16 August 2012 07:09]

Report message to a moderator

Re: function for checking if it is a four digit number [message #563662 is a reply to message #563661] Thu, 16 August 2012 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
Changing the abs comparisons should fix that:
create or replace
  function test_num(
                    p in number
                   )
    return varchar2
      is
          tab sys.OdciNumberList;
      begin
          case
            when abs(p) < 1000 then return 'Invalid number';
            when abs(p) > 9999 then return 'Invalid number';
            when p != trunc(p) then return 'Invalid number';
          end case;
          tab(substr(p,1,1)) := 1;
          tab(substr(p,2,1)) := 1;
          tab(substr(p,3,1)) := 1;
          tab(substr(p,4,1)) := 1;
          if tab.count != 4 then return 'Invalid number'; end if;
          return 'Valid number';
end;
/
Re: function for checking if it is a four digit number [message #563673 is a reply to message #563661] Thu, 16 August 2012 08:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1950
Registered: January 2010
Senior Member
John Watson wrote on Thu, 16 August 2012 08:08
Close, but no cigar!


Oops, smallest 4 digit number with non-repeating digits is 1023, not 1234 and I missed ELSE clause and varray is no good here - need associative array:

create or replace
  function test_num(
                    p in number
                   )
    return varchar2
      is
          type xtype 
            is table of number(1) index by binary_integer;
          tab xtype;
      begin
          case
            when abs(p) < 1023 then return 'Invalid number';
            when abs(p) > 9876 then return 'Invalid number';
            when p != trunc(p) then return 'Invalid number';
            else null;
          end case;
          tab(substr(p,1,1)) := 1;
          tab(substr(p,2,1)) := 1;
          tab(substr(p,3,1)) := 1;
          tab(substr(p,4,1)) := 1;
          if tab.count != 4 then return 'Invalid number'; end if;
          return 'Valid number';
end;
/

Function created.

SQL> select test_num(1023) from dual;

TEST_NUM(1023)
-------------------------------------
Valid number

SQL> 


SY.
Re: function for checking if it is a four digit number [message #563678 is a reply to message #563673] Thu, 16 August 2012 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Doesn't look that way:

SQL> select test_num(12345.6789) from dual;

TEST_NUM(12345.6789)
---------------------------------------------------
Valid number

Yes, it is a valid number, all digits are distinct.

Regards
Michel
Re: function for checking if it is a four digit number [message #563681 is a reply to message #563678] Thu, 16 August 2012 08:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1950
Registered: January 2010
Senior Member
[quote title=Michel Cadot wrote on Thu, 16 August 2012 09:21]Quote:
Yes, it is a valid number, all digits are distinct.


And how many digits it has?

SY.
Re: function for checking if it is a four digit number [message #563685 is a reply to message #563681] Thu, 16 August 2012 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't care, I posted my function to answer the question ("the number should not be repeated") for any number as I specified.

Regards
Michel
Re: function for checking if it is a four digit number [message #563689 is a reply to message #563685] Thu, 16 August 2012 08:51 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

@anilkln

Try like...
create or replace function test_num(v_ip in number) return varchar2 is
v_text varchar2(18);
v_num1 number;
v_num2 number;
v_num3 number;
v_num4 number;
Begin

v_num1:=substr(v_ip,1,1);
v_num2:=substr(v_ip,2,1);
v_num3:=substr(v_ip,3,1);
v_num4:=substr(v_ip,4,1);
  begin
  if length(v_ip)=4 and to_number(v_ip)>0 then
   v_text :='Valid Number';
  end if;
  
  exception when others then 
  v_text :='Invalid Number';
  end;

dbms_output.put_line('result= '|| v_num1|| v_num2||v_num3|| v_num4);
if ( regexp_count(v_ip,v_num1)>=2 or regexp_count(v_ip,v_num2)>=2 
or regexp_count(v_ip,v_num3)>=2  or regexp_count(v_ip,v_num4)>=2 ) then 
v_text :='Invalid Number';
else
v_text :='Valid Number';
end if;
return v_text;
end;
/



This code only for the number...not decimal values..

[Updated on: Thu, 16 August 2012 09:05] by Moderator

Report message to a moderator

Re: function for checking if it is a four digit number [message #563692 is a reply to message #563689] Thu, 16 August 2012 09:06 Go to previous messageGo to next message
Littlefoot
Messages: 19304
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OP uses Oracle 10g; REGEXP_COUNT was introduced in 11g.
Re: function for checking if it is a four digit number [message #563695 is a reply to message #563689] Thu, 16 August 2012 09:12 Go to previous message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
v_num1:=substr(v_ip,1,1);


Implicit conversion => bad!

Quote:
to_number(v_ip)


to_number on a number => silly!

Quote:
exception when others then
v_text :='Invalid Number';


=> STUPID!

Regards
Michel
Previous Topic: Combining overlapping dates
Next Topic: REG:CONVERTING A CLOB DATATYPE to VARCHAR datatype
Goto Forum:
  


Current Time: Thu Jul 24 05:40:50 CDT 2014

Total time taken to generate the page: 0.23094 seconds