Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Select only if two character and two number

Re: Select only if two character and two number

From: Rauf Sarwar <rsarwar_at_ifsna.com>
Date: 1 Jul 2002 23:37:28 -0700
Message-ID: <c2d690f2.0207012237.421819a@posting.google.com>


afayfman_at_mwh.com (Sashafay) wrote in message news:<a13f8a22.0207011552.6e579856_at_posting.google.com>...
> Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3D207850.3C76D4A5_at_exesolutions.com>...
> > Sashafay wrote:
> >
> > > I looking for the function (or some other trick) to identify new input
> > > in the table, that will be a combination of two character and two
> > > number only. But I don't know how to recognize if these input that
> > > what I looking for. In that field user can input any values (ex.
> > > &#8216;ABCD' or &#8216;1234') but I want only first and second characters and
> > > third and fourth numbers (ex. &#8216;AB12').
> > >
> > > Thanks in advance,
> > > Alex
> >
> > I suspect English not being your first language is interfering with your
> > ability to state the problem. But based on my understanding of what you
> > have posted there is not way of identifying records in the manner you
> > suggest. I would suggest posting the table structure and a few rows of
> > data (some new and some old) that will demonstrate what the difference is
> > between old and new records.
> >
> > Generally when I want to do something like this I add a column to the
> > table something like this:
> >
> > ALTER TABLE xyz
> > ADD (new_flag NUMBER(1) DEFAULT 1);
> >
> > This will automatically insert a one '1' into the new_flag column with
> > every insert.
> >
> > Then have your process update the field to 0 as you process the rows.
> >
> > Daniel Morgan
>
> This is for you example:
>
> SELECT* FROM TMP;
>
> ID CD AMT
> ---------- -------- ----------
> 1 AB12 10
> 2 KCST 100
> 3 123454 150
> 4 12TUF24 450
> 5 1Q9KJ0 500
> 6 EQE786 1200
> 7 o0o0o0 675
> 8 AA11BB22 950
> 9 WY09 50
> 10 25OP 775
>
> 10 rows selected.
>
> From SELECT statement I want to get the following output:
>
> SELECT * FROM TMP WHERE ...
>
> ID CD AMT
> ---------- -------- ----------
> 1 AB12 10
> 8 AA11BB22 950
> 9 WY09 50
>
> 3 rows selected.
>
> Understandable???

Now that is much better. You can create a function and call function to test your values. e.g. here is one way to do it,

CREATE OR REPLACE FUNCTION test_func (

   cd_ IN VARCHAR2) RETURN NUMBER
IS

   retval_ NUMBER;
   dummy_ NUMBER;
BEGIN
   BEGIN

      dummy_ := TO_NUMBER(SUBSTR(cd_, 1, 2));
      retval_ := 0;
      EXCEPTION
         WHEN OTHERS THEN
            retval_ := 1;

   END;
   IF (retval_ = 1) THEN
      BEGIN
         dummy_ := TO_NUMBER(SUBSTR(cd_, 3, 2));
         EXCEPTION
            WHEN OTHERS THEN
               retval_ := 0;
      END;

   END IF;
   RETURN retval_;
END test_func;
/

SQL> SELECT * FROM TMP WHERE TEST_FUNC(CD) = 1;         ID CD AMT
---------- -------- ----------

         1 AB12             10
         8 AA11BB22        950
         9 WY09             50
       

3 rows selected.

HTH
//Rauf Sarwar Received on Tue Jul 02 2002 - 01:37:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US