Desktop

Ask something
Discussion Locked

Filter with SQL statement in Orale Srv?

0 helpful
3 replies

by nickoooiiooo

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a problem here that some of you out there could give me a n advice. I am wondering whether there is a function provided by Oracle that allows filtering like 'SELECT ...FROM...WHERE...isnumeric'
I am trying to extract from a field(chartype) that I want those are numeric to be converted into numbers. In other words, I am trying to filter out those which contains any nonnumeric characters.
I know that this would be no prob if I'm using Ms SQL Srv 7.
Does anybody out there can help me?

3 replies

by rodsherer

there are a couple of possible solutions depending upon the sontext and field types you are using. You said it was char type, so I am assuming 1 character. If this is the case, the following statement may work for you:

SELECT *
FROM table_xWHERE ASCII(field_x) BETWEEN ASCII(0) AND ASCII(9);

This will return all the records where the field value is between 0 and 9.

You could also use a stored procedure or function where you loop through the records and attempt to convert the field in question into a number using the TO_NUMBER function. You would use an internal exception block which send it back to the loop and ignores records which can not have the field successfully converted. All records which can have the field convertedcan be processed. This may or may not be usefull, depending upon the context you are working in.

DECLARE

v_number NUMBER;

CURSOR my_cursor IS
SELECT *
FROM table_x;

BEGIN

<> FOR rec IN my_cursor LOOP

BEGIN

v_number := TO_NUMBER(rec.field_x);

EXCEPTION
WHEN OTHERS THEN
GOTO validate_next_record;

END;

-- process record

END LOOP validate_next_record;

END;

I hope this is helpfull

Rod

This discussion is closed to new replies.