Home » SQL & PL/SQL » SQL & PL/SQL » String validation
String validation [message #229961] Tue, 10 April 2007 07:23 Go to next message
kanis
Messages: 61
Registered: November 2006
Member
Need to check an input string in a proc whether all the character of input string are a-z, A-Z, 0-9 and '/'.
Sould I check this string by one by one character(this may take much time) . or any other way is there to do this quickly(check all the char in the string in a bulk)?

please help

[Mod-edit]Changed the title. Choose an appropriate title next time.

[Updated on: Tue, 10 April 2007 07:25] by Moderator

Report message to a moderator

Re: String validation [message #229965 is a reply to message #229961] Tue, 10 April 2007 07:44 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
i don't know much but it can give u result...
here are some brilliant people who are willing to help us.

declare
  the_string varchar2(100) := 'Number-10';
  i number:=1;
begin
  while i <= length(the_string)
  loop
     if substr(the_string,i,1) between chr(48) and chr(57) OR
        substr(the_string,i,1) between chr(65) and chr(90) OR
        substr(the_string,i,1) between chr(97) and chr(122) OR
        substr(the_string,i,1) = chr(47)
     THEN
        dbms_output.put_line ('ok');
     else
        dbms_output.put_line ('not ok');
     end if;
     i:=i+1;
  end loop;
end;


result
ok
ok
ok
ok
ok
ok
not ok
ok
ok

PL/SQL procedure successfully completed.

[Updated on: Tue, 10 April 2007 07:45]

Report message to a moderator

Re: String validation [message #229967 is a reply to message #229965] Tue, 10 April 2007 07:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I admit: I was too lazy to search the board too, although I'm pretty confident that this has been addressed before. Here's a quick and dirty solution.

SQL> ed
Wrote file afiedt.buf

  1  with yourtable as
  2   (
  3    select '123445678' thestring from dual union all
  4    select 'abc/12345' thestring from dual union all
  5    select '1123-4567' thestring from dual
  6   )
  7  select thestring
  8  from   yourtable
  9  where  replace( translate( upper(thestring)
 10                           , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789/'
 11                           ,chr(0)
 12                           )
 13                , chr(0)
 14*               ) is null -- valid combinations
SQL> /

THESTRING
---------
123445678
abc/12345

SQL> ed
Wrote file afiedt.buf

  1  with yourtable as
  2   (
  3    select '123445678' thestring from dual union all
  4    select 'abc/12345' thestring from dual union all
  5    select '1123-4567' thestring from dual
  6   )
  7  select thestring
  8  from   yourtable
  9  where  replace( translate( upper(thestring)
 10                           , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789/'
 11                           ,chr(0)
 12                           )
 13                , chr(0)
 14*               ) is not null -- invalid combinations
SQL> /

THESTRING
---------
1123-4567

SQL>
Just look at the where clause. If you're on 10g, you could play around with regulare expressions. That would make it even more fun.

MHE
Re: String validation [message #230134 is a reply to message #229961] Wed, 11 April 2007 00:43 Go to previous message
oralover
Messages: 97
Registered: January 2005
Member
Maaher's solution is best...thanks.
Previous Topic: to get different format of dates for the user input
Next Topic: Hierachical Query - ORA-01436 Error
Goto Forum:
  


Current Time: Sat Dec 03 20:21:45 CST 2016

Total time taken to generate the page: 0.08160 seconds