| PL/SQL Issue [message #561094] |
Wed, 18 July 2012 15:09  |
 |
akull
Messages: 27
Registered: July 2012
Location: Argentina
|
Junior 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  |
joy_division
Messages: 4265
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 |
 |
Littlefoot
Messages: 16928
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
SQL> select level, chr(level) v_char
2 from dual
3 connect by level < 255;
LEVEL V_CH
---------- ----
1 ☺
2 ☻
3 ♥
4 ♦
<snip>
42 *
43 +
44 ,
45 -
46 .
47 /
48 0
49 1
50 2
51 3
52 4
53 5
54 6
55 7
56 8
57 9
58 :
59 ;
60 <
61 =
62 >
63 ?
64 @
65 A
66 B
67 C
68 D
<snip>
90 Z
91 [
92 \
93 ]
94 ^
95 _
96 `
97 a
98 b
99 c
100 d
101 e
102 f
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 |
|
|
|
] 
Members
Search
Help
Register
Login
Home
