Check if part of a string has only number values

I had the requirement to find out if a part of a string includes only number values:

Table: My_Test_String_Table
Column: T_STR
String 1: TIGER031STD
String 2: TIGERTT1STD
String 3: TIGER999STD

Now I need to find all rows where position 6, 7 and 8 is a number value.

Select:


select * from My_Test_String_Table
where 
decode(REGEXP_INSTR (substr(T_STR,6,3), '[^[:digit:]]'),0,'YES','NO') = 'YES';

Returns String 1 and 3!

Take a look into this forum post to find more examples:
http://forums.oracle.com/forums/thread.jspa?threadID=371057

Check if part of a string has only number values

Post Tags: