Is there a standard SQL function for comparing the format of a character column? I’m trying to get a list of records where the phone number is not in the format XXX-XXX-XXXX. My SQL skill are a little rusty, and I can’t remember if there is a simple way to do this.
Use ‘not like’:
select * from [table] where [field] not like ‘--____’
The underscore character is a wildcard that will match any single character. This should work.
You, my friend, are a god (at least for one day). Thanks.
What database are you using? Do you want to verify that the digits are actually numeric digits?
Have you considered the possibility of foreign phone numbers?
I’m using Oracle 8i. Ideally, I would like to verify that they are numeric digits (###-###-####), but I also didn’t want to spend too much time coding for it. I’m also not concerned about foreign phone number in this instance.
create package check_func
is
function valid_num (num_in varchar2) return char ;
function valid_usa_phone (phone_in varchar2) return char ;
end check_func ;
/
create package body check_func
is
function valid_num (num_in varchar2)
return char
is
x pls_integer ;
begin
x := to_number (num_in) ;
return 'Y' ;
exception
when value_error then
return 'N' ;
end ;
function valid_usa_phone (phone_in varchar2)
return char
is
valid boolean := true ;
begin
valid := length (phone_in) = 12
and substr (phone_in, 4, 1) = '-'
and substr (phone_in, 8, 1) = '-'
and valid_num (substr (phone_in, 1, 3)) = 'Y'
and valid_num (substr (phone_in, 5, 3)) = 'Y'
and valid_num (substr (phone_in, 9, 4)) = 'Y' ;
if valid
then
return 'Y' ;
else
return 'N' ;
end if ;
end ;
end check_func ;
/
depending on how the functions are used, you could change them to return boolean instead of Y/N.
Umm, I think the OP was asking about standard SQL.
Sorry, Lord Derfel, you are still a god, but Arnold is a higher order god (which we already knew).
Thanks so much for that, Arnold. I couldn’t get it to work as a package, so I just create the functions stand-alone. It’s been too long since I’ve programmed in SQL that I’ve forgotten stuff.
You can use not like to test if a character is a valid number - instead of the underscore, use …not like ‘[0-9][0-9][0-9]-[0-9]…’
Granted, that makes your query string kind of unwieldy, but that will also work.
where translate(phone_no,‘0123456789’,‘’)!='--’)
Works with Oracle and checks for numbers
For number that may have parenthesis around the area code,
you can do this
translate(phone_no,‘0123456789)(’,‘-’)!='--’)
The translate() function does a one-to-one replacement of characters in the second argument with characters in the third. So, 0-9 translates to ‘*’, ‘)’ to ‘-’ and ‘(’ gets eliminated. That’s why I used ‘)(’ instead of ‘()’
That the kind of thing I was thinking of originally. It’s quick and simple. Arnold’s has the advantage of being reusable, but they both work.
You’re right bnorton. But as we see JeffB found my example useful, so there. 
NotMrKnowItAll, allow me to add a condition to your short and clever example:
phone_no != '***-***-****' and
translate(phone_no,'0123456789','**********')!='***-***-****')
to prevent the string ‘--****’ from being considered a valid phone number.
[Edited by Arnold Winkelried on 11-16-2001 at 04:28 PM]
JeffB, it should have worked fine as a package. Were you unable to compile it? Or did you forget to refer to the function by package_name.function_name?
SQL> select
2 check_func.valid_usa_phone ('123-456-7890')
3 from dual ;
CHECK_FUNC.VALID_USA_PHONE('123-456-7890')
------------------------------------------------
Y
Oops! Just thought of something else. The “valid_num” function in my example checks that a string is a valid Oracle number literal, so it’s not a good thing to use it for the phone number check, since something like ‘1.2’ or ‘3E5’ would be considered a valid number. NotMrKnowItAll’s version is the correct one.
[Edited by Arnold Winkelried on 11-16-2001 at 04:39 PM]
Well, what the heck is that anyway? Is it some sort of Oracle proprietary language?
That was Oracle’s built-in “programming” language, PL/SQL.
Oh. I take it that is what is used for writing stored procedures for Oracle.