|
Re: check string for numbers and special characters [message #175688 is a reply to message #175686] |
Sun, 04 June 2006 19:10   |
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 #175787 is a reply to message #175732] |
Mon, 05 June 2006 06:41   |
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 #412640 is a reply to message #175686] |
Fri, 10 July 2009 04:56   |
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   |
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  |
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'
|
|
|