Hi All ,
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production
I have string value around 15000 records, I need to pick those record having length of 12 nad identify the position of character & number.
Ex: Record Set
=================
A12345T11111
A12345F12222
A12345F13333
A12345F14444
In this case the first character is an alphabet followed by 5 numbers and 7 th position is a alphabet and followed by 5 numbers..
I have pick these format record only..
There are some records like these as well,
--In the Below example the 2nd charater is a alphabet its invalid
AA2345512345
-- In this , the 7th character is not a alphabet
BA2345512345
These records have to be eliminated....
Valid Format : X99999X99999
==============
Invalid Format: XX9999999999,X99999999999,XX999999999X
I have a tried the following query , which gives me a result whether string has a alphabet or not , if present how many alphabets are present.
This helped me identifying the how many characters present, but I need to get the positions...
select LENGTH('A12345T11111') LEN#, LENGTH(TRANSLATE('A12345T11111','A1234567890','A')) from dual where LENGTH(TRANSLATE('A12345T11111','A1234567890','A'))=2
ANy ideas or thoughts on the same..
Thanks
Ananda