| Selecting an alpha character from a varchar2 field containing mostly numbers [message #374505] |
Fri, 15 June 2001 04:42  |
John Guy
Messages: 8
Registered: June 2001
|
Junior Member |
|
|
When I try to to_number a varchar2 field I get an invalid number error, I assume one of the fields is holding an alpha charater. Is there a special character that can be used in the where claus to indicate an alpha charater search i.e what can I replace the question mark with in this where claus....
Where attribute2 LIKE '%?%'
|
|
|
|
| Re: Selecting an alpha character from a varchar2 field containing mostly numbers [message #374522 is a reply to message #374505] |
Fri, 15 June 2001 18:30  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
see translate() and replace() functions. They are quite similar but not the same. Also see the to_num() function below for ideas.
CREATE OR REPLACE FUNCTION to_num (v_value IN VARCHAR2)
RETURN NUMBER
IS
v_retval NUMBER;
BEGIN
v_retval := TO_NUMBER (v_value);
RETURN v_retval;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;-- Whoops!
END to_num;
/
select .... from ...
where to_num(varchar_col) is null
and varchar_col is not null;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
