Home » SQL & PL/SQL » SQL & PL/SQL » check string for numbers and special characters  () 1 Vote
check string for numbers and special characters [message #175686] Sun, 04 June 2006 16:43 Go to next message
wucis
Messages: 60
Registered: March 2005
Member
How can I check a string if it is compliant to the following demands : if for e.g. phone numbers can consist only of numbers and the characters / and +
Re: check string for numbers and special characters [message #175688 is a reply to message #175686] Sun, 04 June 2006 19:10 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Do you mean as a filter in a SQL statement...
SELECT *
FROM   your_table
WHERE  REPLACE(TRANSLATE(candidate_phone_number
               ,         '0123456789/+'
               ,         '999999999999')
       ,       '9') IS NULL
/
... or as a check constraint?
ALTER TABLE your_table ADD CONSTRAINT phone_chars_only
CHECK (REPLACE(TRANSLATE(candidate_phone_number
               ,         '0123456789/+'
               ,         '999999999999')
       ,       '9') IS NULL)
/
Re: check string for numbers and special characters [message #175693 is a reply to message #175688] Sun, 04 June 2006 22:13 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member
Art's first query can be rewritten as

SELECT *
  FROM your_table
 WHERE TRANSLATE (candidate_phone_number, '@0123456789/+', '@') IS NULL

Query Your Dream & Future at
http://www.soqool.com
Re: check string for numbers and special characters [message #175732 is a reply to message #175693] Mon, 05 June 2006 03:11 Go to previous messageGo to next message
wucis
Messages: 60
Registered: March 2005
Member
Maybe you misunderstood me:
I'd like to test (perhaps with a function) a given string '+44 0123@{376/8987}'
if it contains only numbers and / and +
If the test fails, an error should be raised.
I do not know how to handle this in an elegant, fast way.
Re: check string for numbers and special characters [message #175787 is a reply to message #175732] Mon, 05 June 2006 06:41 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Well, why didn't you say so?
SQL> CREATE OR REPLACE FUNCTION has_phone_number_chars (
  2      p_candidate_phone_number    IN  VARCHAR2
  3  ,   p_phone_number_chars        IN  VARCHAR2 DEFAULT '0123456789+/'
  4  )
  5  RETURN VARCHAR2
  6  IS
  7      l_is_valid      VARCHAR2(1);
  8  BEGIN
  9      IF (TRANSLATE (p_candidate_phone_number
 10          ,          CHR(1) || p_phone_number_chars
 11          ,          CHR(1)) IS NULL) THEN
 12          l_is_valid := 'Y';
 13      ELSE
 14          l_is_valid := 'N';
 15      END IF;
 16      RETURN (l_is_valid);
 17  END has_phone_number_chars;
 18  /
 
Function created.
 
SQL> CREATE TABLE t (candidate_phone_number VARCHAR2(30));
 
Table created.
 
SQL> INSERT INTO t VALUES ('+44 0123@{376/8987}');
SQL> INSERT INTO t VALUES ('+440123@{376/8987}');
SQL> INSERT INTO t VALUES ('+44 0123/44539');
SQL> INSERT INTO t VALUES ('+440123/44539');
 
SQL> COL "IS_VALID?" FORMAT A10
SQL> SELECT candidate_phone_number
  2  ,      has_phone_number_chars(candidate_phone_number) "IS_VALID?"
  3  FROM   t
  4  /
 
CANDIDATE_PHONE_NUMBER         IS_VALID?
------------------------------ ----------
+44 0123@{376/8987}            N
+440123@{376/8987}             N
+44 0123/44539                 N
+440123/44539                  Y
 
SQL>
Note, only the last of the four is valid, because the others all have spaces (among other things, potentially), and you did not list a space as a valid phone-number character.
Re: check string for numbers and special characters [message #176310 is a reply to message #175787] Wed, 07 June 2006 16:35 Go to previous messageGo to next message
wucis
Messages: 60
Registered: March 2005
Member
Thanks Art,

I could use your way of handling the special characters.
icon5.gif  Re: check string for numbers and special characters [message #412640 is a reply to message #175686] Fri, 10 July 2009 04:56 Go to previous messageGo to next message
poshkid
Messages: 2
Registered: July 2009
Location: Sri Lanka
Junior Member
First of all, I like to thank all who contributed to this thread.

I have changed the above to check the length of the input (12 characters) and also to check whether it contains special charcters other than numbers.

If the above conditions are not met, the cunction gives out "N".
If its ok, it gives out "Y"
Code:


CREATE OR REPLACE FUNCTION cmb_valid_act (
      cmb_act_number    IN  VARCHAR2,
      cmb_act_number_chars IN  VARCHAR2 DEFAULT '0123456789'  )
      RETURN VARCHAR2
      IS
      a_is_valid      VARCHAR2(1);
  BEGIN
      cmb_act_number=to_char(
      IF (TRANSLATE (cmb_act_number,CHR(1) || cmb_act_number_chars,CHR(1)) IS NULL) THEN
          IF (LENGTH(cmb_act_number) = 12) THEN
          a_is_valid := 'Y';
          ELSE
          a_is_valid := 'N';
          END IF;
      ELSE
          a_is_valid := 'N';
      END IF;
      RETURN (a_is_valid);
  END cmb_valid_act;



But the issue is when I try to check a sting staring with zero but has 12 numeric characters, it gives out "N" > which is wrong..

Can anybody help to fix this issue. Thanks.

[Updated on: Fri, 10 July 2009 05:25]

Report message to a moderator

Re: check string for numbers and special characters [message #412652 is a reply to message #412640] Fri, 10 July 2009 05:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Then you must be running code other than that which you have posted.
Given that the code you posted doesn't compile, this is a fairly safe bet.

I get:
SQL> CREATE OR REPLACE FUNCTION cmb_valid_act (
  2        cmb_act_number    IN  VARCHAR2,
  3        cmb_act_number_chars IN  VARCHAR2 DEFAULT '0123456789'  )
  4        RETURN VARCHAR2
  5        IS
  6        a_is_valid      VARCHAR2(1);
  7    BEGIN
  8        IF (TRANSLATE (cmb_act_number,CHR(1) || cmb_act_number_chars,CHR(1)) IS NULL) THEN
  9            IF (LENGTH(cmb_act_number) = 12) THEN
 10            a_is_valid := 'Y';
 11            ELSE
 12            a_is_valid := 'N';
 13            END IF;
 14        ELSE
 15            a_is_valid := 'N';
 16        END IF;
 17        RETURN (a_is_valid);
 18    END cmb_valid_act;
 19    /

Function created.

SQL> 
SQL>   select cmb_valid_act('123456789012') from dual;

CMB_VALID_ACT('123456789012')
------------------------------------------------------------------------------------------------
Y

SQL>   
SQL>   select cmb_valid_act('012345678901') from dual;

CMB_VALID_ACT('012345678901')
------------------------------------------------------------------------------------------------
Y
Re: check string for numbers and special characters [message #412844 is a reply to message #412652] Mon, 13 July 2009 01:17 Go to previous message
poshkid
Messages: 2
Registered: July 2009
Location: Sri Lanka
Junior Member
It's working as you said if the input is given between ' '.

But when I call this function from within an another SQL and try to check it, it gives 'N' as output for any number starting with zero, but has 12 digits.

e.g. I want to select numbers which are not valid as follows:
select * from test1 where
cmb_valid_act(test1.PAYEE_ACCT) = 'N'
Previous Topic: connecting 9i with visual studio2005
Next Topic: Regarding oracle jobs
Goto Forum:
  


Current Time: Thu Feb 13 23:14:33 CST 2025