check for numeric value in varchar2 field & number validation 1

[rk68]

Hi,
I have varchar2 field in a table which has data in number/alpha numeric field.
I want to extract records which has only numeric field with validation like the number should be between 1 & 10.

The sample data is given below.
A12
7
bb
5
4
c45
11

So out of the above 7 records, I need a query which display data 7,5,4 only bcoz the I need numbers between 1 & 10 only.
I tried translate(t.trn_matl_cd,'_0123456789','_') but it works for 1st step i.e. display all number fields but how do I do the validation to get number between 1 & 10.

Thanks & regards,
RAJ

 

[Andrzejek]

How about:
[tt]
Select FieldA
From MyTable
Where FieldA Between 1 And 10[/tt]




---- Andy

There is a great need for a sarcasm font.

 

[Beilstwh]

CREATE TABLE TEST
(COL1 VARCHAR2(20 BYTE));

SELECT * FROM TEST;

COL1
--------------------
A12
7
bb
5
4
C45
11

7 rows selected.

SELECT COL1
FROM TEST
WHERE REGEXP_LIKE(COL1,'^(?:1|2|3|4|5|6|7|8|9|10){1,2}$');



COL1
--------------------
7
5
4

SQL>


Bill
Lead Application Developer
New York State, USA

 

[rk68]

Thanks Andy & Bill.
@Bill - your query did work but when I added 1 in the data, it didn't show display after executing the query.

So I used the below SQL & it did display 1.
SELECT COL1
FROM TEST where
REGEXP_LIKE(COL1,'^(?:0|1|2|3|4|5|6|7|8|9|10)$')