how to accept mutiple formats for inputing?

I am developing an Oracle 8i form. You know in Oracle form, you can query the result by doing "Enter->input into field-> run" to display  result


I have an input query field. So far it accept format like 28171110

Now I want it also accept 2817-1110, and L2817-1110

That means when input 2817-1110, and L2817-1110, it will automatically convert it to 28171110 format

How to do that?
wasabi3689Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
where are you doing your processing?  Inside the form? or in pl/sql on the server?

8i isn't a forms version,  they went from 6i to 9i and 10g.

0
sdstuberCommented:
If your database is 8i

then try this...
 your_field :=
        TRANSLATE(
            your_field,
            '0123456789' || TRANSLATE(your_field, CHR(0) || '0123456789', CHR(0)),
            '0123456789'
        );

Open in new window

0
wasabi3689Author Commented:
where I put this code to? under the Find button trigger or ...
The form version is 11.0.0

the field is license_number

Here is the image
find-download.JPG
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
wherever you want.  You could put it behind the find button, if you pass the string to a pl/sql procedure, you could put the code inside that procedure, you could pass it to a function get the stripped string back and pass that to whatever you want.

0
wasabi3689Author Commented:
I have the following error

Compiling WHEN-BUTTON-PRESSED trigger on FIND item in FIND_DOWNLOADS block...
Compilation error on WHEN-BUTTON-PRESSED trigger on FIND item in FIND_DOWNLOADS
block:
PL/SQL ERROR 303 at line 6, column 1
qualifier 'FIND_DOWNLOADS' must be declared
PL/SQL ERROR 0 at line 6, column 1
Statement ignored


attached the code is for Find button (when-button-pressed)
:parameter.G_query_find := 'TRUE';
app_find.find('XX_DOWNLOADS_V');
:parameter.G_query_find := 'FALSE';
 
 
find_downloads.license_number :=
        TRANSLATE(
            license_number,
            '0123456789' || TRANSLATE(your_field, CHR(0) || '0123456789', CHR(0)),
            '0123456789'
        );

Open in new window

0
wasabi3689Author Commented:
any ideas?
0
wasabi3689Author Commented:
I have the following code dealt with the same issue in other form. I don't know how to make an arrangment for it. Can you modify it and where it put to if you don't have any ideas.

    -- This will allow users to query by license numbers including the L or -
   
	IF :find_downloads.license_number IS NOT NULL THEN
        IF INSTR(:find_downloads.license_number,'L',1) > 0 OR INSTR(:find_downloads.license_number,'-',1)  > 0 THEN
	    :find_downloads.license_number := REPLACE(REPLACE(:find_downloads.license_number,'L',''),'-','');
        END IF;
        END IF;
 
 
 
    IF :parameter.G_query_find = 'TRUE' THEN      
       
        IF :find_downloads.license_number IS NOT NULL THEN
	    IF v_where_clause IS NULL THEN
	        v_where_clause := v_where_clause ||'license_number= '||replace(replace(:find_downloads.license_number,'L',''),'-','');
	    ELSE
	        v_where_clause := v_where_clause ||' and license_number= '||replace(replace(:find_downloads.license_number,'L',''),'-','');
	    END IF;
        END IF;
 
     END IF; 

Open in new window

0
sdstuberCommented:
how about something like this...  (You left out the colon, then left out the find_downloads then left the your_field in.  all 3 places need to be the full ":find_downloads.license_number"




:find_downloads.license_number :=
        TRANSLATE(
            :find_downloads.license_number,
            '0123456789' || TRANSLATE(:find_downloads.license_number, CHR(0) || '0123456789', CHR(0)),
            '0123456789'
        );


or, if L and - are the only special characters you'll ever need to worry about then you cand do the double replace as you did above
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wasabi3689Author Commented:
excellent
0
sdstuberCommented:
glad I could help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.