Re: Select only if two character and two number

Charlie3101_at_hotmail.com (Charlie Edwards) wrote in message news:<db479d88.0207020814.56a2995a_at_posting.google.com>...
> rsarwar_at_ifsna.com (Rauf Sarwar) wrote in message news:<c2d690f2.0207012237.421819a_at_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
>
> 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

> will get you what you want without all that PL/SQL!

Correct. There is always more then one way to solve a problem. I would favor a function in this instance atleast for scalabilty and code management. Even though both queries probably cost the same (Full table scan), function will take care of both UPPER/lower cases...Plus deploy it once, use it from anywhere.

Just thought I will put my two cents in -:)

//Rauf Sarwar Received on Tue Jul 02 2002 - 21:23:43 CDT