Re: Select only if two character and two number

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Tue, 2 Jul 2002 15:25:58 -0400
Message-ID: <DanU8.5246$68.137523@news4.srv.hcvlny.cv.net>

Just in case ... expanding from the previous post ..
  1 SELECT 'MATCHED' FROM tmp
  2 WHERE REPLACE(TRANSLATE(cd,
'BCDEFGHIJKLMNOPQRSTUVWXYZ123456789',

  3*   'AAAAAAAAAAAAAAAAAAAAAAAAA000000000'),

'AA00') IS NULL will take care of cases like 'AA12X' or
'AB12XY99A' being filtered out ....

Anurag

"Charlie Edwards" <Charlie3101_at_hotmail.com> wrote in message
> > > Daniel Morgan <dmorgan_at_exesolutions.com>
wrote in message
> > > > 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
> > file://Rauf Sarwar
>
> I think
>
> SELECT * FROM tmp
> WHERE WHERE TRANSLATE(cd,
>   'BCDEFGHIJKLMNOPQRSTUVWXYZ123456789',
>   'AAAAAAAAAAAAAAAAAAAAAAAAA000000000') LIKE

'AA00%'
>
> will get you what you want without all that PL/SQL!
>
> CE Received on Tue Jul 02 2002 - 14:25:58 CDT