Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Issue (Oracle 11g)
PL/SQL Issue [message #561094] Wed, 18 July 2012 15:09 Go to next message
akull
Messages: 39
Registered: July 2012
Location: Argentina
Member
Hi Guys!

I will apreciate if someone can help me with my issue, I've got a logic problem, here is the situation, I have to write a function that receive as parameter a datatype varchar2 and validate if the letter is less than "A" or greater than "Z", it must print do not correct, or correct depending on the situation, no problem so far, the only problem I've got is: If the symbol is ";" is valid, but i do not know how to write it, It does not depend on the PL/SQl languaje but depends on the logic itself.


DECLARE

v_nombre    varchar2(20):='john;%';
vv          varchar2(1);

begin

for i in 1..length(v_nombre) loop

vv:=substr(v_nombre,i,1);

IF (vv < 'a' or vv > 'z') then

dbms_output.put_line('do not correct' || ' ' || vv);

else

dbms_output.put_line('correct' || ' ' || vv);

end if;

end loop;

end;


Re: PL/SQL Issue [message #561098 is a reply to message #561094] Wed, 18 July 2012 15:29 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
you said A and Z, but your code does a and z. these are different.
why not use the ascii code for the range?
pl/sql runs within the database and is not interactive. having it display messages is ok for debugging while running at a sql prompt, but an application will not be bale to display any messages.

[added]
oh, and ascii code for semicolon is 59, so you would just an *or* clause.

[Updated on: Wed, 18 July 2012 15:31]

Report message to a moderator

Re: PL/SQL Issue [message #561104 is a reply to message #561098] Wed, 18 July 2012 16:51 Go to previous message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure I understood which values are valid and which are not, but OK - here's one option. See if you can adjust it.

Here's how you can find out characters and their ASCII codes:
Click here

And the query ("valid" would be characters between A and Z, as you originally said):
SQL> select ascii('A') "A",
  2         ascii('a') "a",
  3         ascii('Z') "Z",
  4         ascii('z') "z"
  5  from dual;

         A          a          Z          z
---------- ---------- ---------- ----------
        65         97         90        122

SQL> with test as
  2    (select '&value' col from dual)
  3  select
  4    col,
  5    substr(col, level, 1) one_char,
  6    case when ascii(substr(col, level, 1)) between 65 and 90 then 'Valid'
  7         else 'Invalid'
  8    end status
  9  from test
 10  connect by level <= length(col);
Enter value for value: JoHN;/#
old   2:   (select '&value' col from dual)
new   2:   (select 'JoHN;/#' col from dual)

COL     ONE_ STATUS
------- ---- -------
JoHN;/# J    Valid
JoHN;/# o    Invalid
JoHN;/# H    Valid
JoHN;/# N    Valid
JoHN;/# ;    Invalid
JoHN;/# /    Invalid
JoHN;/# #    Invalid

7 rows selected.

SQL> /
Enter value for value: MIke
old   2:   (select '&value' col from dual)
new   2:   (select 'MIke' col from dual)

COL  ONE_ STATUS
---- ---- -------
MIke M    Valid
MIke I    Valid
MIke k    Invalid
MIke e    Invalid

SQL>

[Updated on: Wed, 18 July 2012 16:54]

Report message to a moderator

Previous Topic: How to join a table column names with data from other table
Next Topic: REGEXP_LIKE
Goto Forum:
  


Current Time: Sat Apr 19 20:12:31 CDT 2014

Total time taken to generate the page: 0.10513 seconds