Re: Need help with user prompt

"BT" <threepio23_at_yahoo.com> wrote in message news:<auvfkv$cip$1_at_sparta.btinternet.com>...
> I am making some SQL code that I want to ask the user to input the first two
> letters of a postcode, and it will then find the details of all the
> addresses containing these two letters as the first two digits in the
> postcode. I have adapted some code I got from Oracle.com and here it is:
>
> PROMPT Enter the first two digits of your postcode
> PROMPT For Example ME
> ACCEPT ENUMBER NUMBER PROMPT 'Postcode. :'
> SELECT SUPNAME, SUPADD
> FROM SUP
> WHERE SUPPCODE=&ENUMBER
>
> a postcode contains 6 digits with a space in the middle, for example, ME6
> 7HQ. I want the user to type in say "ME" and get all the addresses that
> start like this.
>
> Also, I know that this code will only work with numbers, so any help on code
> that will actually do this using characters would be great.
>
> Thanks

If I understand correctly, SUPPCODE holds values like ME6 7HQ then why are you using NUMBER prompt? It is a VARCHAR2 datatype.

PROMPT Enter the first two digits of your postcode PROMPT For Example ME
ACCEPT ENUMBER PROMPT 'Postcode. :'
SELECT SUPNAME, SUPADD
FROM SUP
-- To eliminate Mixed case possibilities, use UPPER/LOWER on both sides. Also using LIKE with % wild card will return everything beginning with e.g. ME
WHERE UPPER(SUPPCODE) LIKE UPPER('&ENUMBER%'); Suggestion: Buy a Oracle SQL reference book from Amazon.com

Regards
/Rauf Sarwar Received on Wed Jan 01 2003 - 21:35:22 CST