basic plsql question

[jmaxsherkimer]

i had a basic question on plsql, if i have a variable as varchar2,
containing some text like 'A12345', how do i write a function to make
sure that the first char. is the letter 'A' or 'a' and the next five
characters are numbers? sorry about asking a basic question, but i'm
not the dba here, he recently left and i'm just the web developer
having to do this for now.

[Jim Kennedy]

Re: basic plsql question


"jmaxsherki mer" <jmaxsherkimer@ hotmail.comwrot e in message
news:f8606b97.0 408270420.167b3 2fb@posting.goo gle.com...

i had a basic question on plsql, if i have a variable as varchar2,
containing some text like 'A12345', how do i write a function to make
sure that the first char. is the letter 'A' or 'a' and the next five
characters are numbers? sorry about asking a basic question, but i'm
not the dba here, he recently left and i'm just the web developer
having to do this for now.

Look in the docs you can find documentation at otn.oracle.com. Look in the
SQL Reference guide. (there are a ton of docs, but that is the one you want)
You will probably use substr and translate. You can test it in sqlplus by


select substr('A12345' ,1,1) from dual;

etc.
That will allow you to evaluate the expression so you can test your function
quickly. Once you are satisfied with your exprerssion you can put the
expression in your code.
Jim

[Wario]

Re: basic plsql question

This function will let you test the format any string up to 50
characters. You may modify is needed.

function word_format (word varchar2) return varchar2 is
fmt varchar2(50);
ltr char(1);
begin
for x in 1..length(word) loop
ltr := substr(word,x,1 );
fmt := fmt || case
when ltr between 'A' and 'Z' then 'X'
when ltr between 'a' and 'z' then 'X'
when ltr between '0' and '9' then '9'
else ltr
end;
end loop;

return fmt;
end;
/

if word_format('A5 4698') = 'X99999' then
.....
end if;

[Wario]

Re: basic plsql question

This procedure is better. You could use the translate function alone,
but the string parameters would make your code long and unwieldy.

create function word_fmt (word varchar2) return varchar2 is
begin
return translate(word,
'abcdefghijklmn opqrstuvwxyzABC DEFGHIJKLMNOPQR STUVWXYZ0123456 789',
'XXXXXXXXXXXXXX XXXXXXXXXXXXXXX XXXXXXXXXXXXXXX XXXXXXXX9999999 999'
);
end;

if word_fmt('a5213 2') = 'X99999' then
.....
end if;

or

if translate(word, 'abcdefghijklmn opqrstuvwxyzABC DEFGHIJKLMNOPQR STUVWXYZ0123456 789',
'XXXXXXXXXXXXXX XXXXXXXXXXXXXXX XXXXXXXXXXXXXXX XXXXXXXX9999999 999') =
'X99999' then
....
end if;

[Pedro Lopes]

Re: basic plsql question

You can use regular expressions on 10g.

see the documentation at http://tahiti.oracle.com

bye,
pedro


jmaxsherkimer wrote:

i had a basic question on plsql, if i have a variable as varchar2,
containing some text like 'A12345', how do i write a function to make
sure that the first char. is the letter 'A' or 'a' and the next five
characters are numbers? sorry about asking a basic question, but i'm
not the dba here, he recently left and i'm just the web developer
having to do this for now.

[Alex Filonov]

Re: basic plsql question

r0cky@insightbb .com (Wario) wrote in message news:<9204a53e. 0408301136.51b3 3314@posting.go ogle.com>...

This procedure is better. You could use the translate function alone,
but the string parameters would make your code long and unwieldy.
>
create function word_fmt (word varchar2) return varchar2 is
begin
return translate(word,
'abcdefghijklmn opqrstuvwxyzABC DEFGHIJKLMNOPQR STUVWXYZ0123456 789',
'XXXXXXXXXXXXXX XXXXXXXXXXXXXXX XXXXXXXXXXXXXXX XXXXXXXX9999999 999'
);
end;
>

First character should be 'a' or 'A', other four characters are digits,
so translate function call can look like

translate(word, 'aA0123456789', 'AA9999999999')

if word_fmt('a5213 2') = 'X99999' then
.....
end if;
>
or
>
if translate(word, 'abcdefghijklmn opqrstuvwxyzABC DEFGHIJKLMNOPQR STUVWXYZ0123456 789',
'XXXXXXXXXXXXXX XXXXXXXXXXXXXXX XXXXXXXXXXXXXXX XXXXXXXX9999999 999') =
'X99999' then
....
end if;