INST Command and help breaking apart Syntax (SQL/Oracle

lstrudeman

Hello;

A friend gave me this syntax and they are unavailable at the moment and I need this asap. I am trying to figure out how SQL figures this out. Below the syntax takes a field in a file and breaks apart the pieces of the key for other uses. The field looks like this 1234567*AWD*07FA (or 1234567*FPER*07FA). I just need to understand how the numbers behind the TA_2007_ID field work to break apart they key?? The first one is easy.. it just gets the first 7 numbers. The first part is the only one where it is consistantly 7 numbers.. the others vary depending on the award.

Thanks in advance...

SELECT a.* ,substr(a.TA_2007_ID, 1,7) as student_id

,substr(a.TA_2007_ID,INSTR(a.TA_2007_ID,'*', 1, 1)+1,INSTR(a.TA_2007_ID,'*',1,2)-INSTR(a.TA_2007_ID,'*', 1, 1)-1) as award_only

,substr(a.TA_2007_ID,INSTR(a.TA_2007_ID,'*', 1, 2)+1,INSTR(a.TA_2007_ID,'*',1,2)-INSTR(a.TA_2007_ID,'*', 1, 1)+4) as term_only

FROM COLL_PRODUCTION.TA_2007 A

Jun 6 '07 #1

Saii

Hello;

A friend gave me this syntax and they are unavailable at the moment and I need this asap. I am trying to figure out how SQL figures this out. Below the syntax takes a field in a file and breaks apart the pieces of the key for other uses. The field looks like this 1234567*AWD*07FA (or 1234567*FPER*07FA). I just need to understand how the numbers behind the TA_2007_ID field work to break apart they key?? The first one is easy.. it just gets the first 7 numbers. The first part is the only one where it is consistantly 7 numbers.. the others vary depending on the award.

Thanks in advance...

SELECT a.* ,substr(a.TA_2007_ID, 1,7) as student_id

,substr(a.TA_2007_ID,INSTR(a.TA_2007_ID,'*', 1, 1)+1,INSTR(a.TA_2007_ID,'*',1,2)-INSTR(a.TA_2007_ID,'*', 1, 1)-1) as award_only

,substr(a.TA_2007_ID,INSTR(a.TA_2007_ID,'*', 1, 2)+1,INSTR(a.TA_2007_ID,'*',1,2)-INSTR(a.TA_2007_ID,'*', 1, 1)+4) as term_only

FROM COLL_PRODUCTION.TA_2007 A

hi,

this uses * as the reference for finding the string pieces between and after the *. The second column in select list picks data between starting point and ending point of * and third column picks the data after second *.

hope that helps!!!
note: you can work with individual pieces to understand better

Jun 6 '07 #2

debasisdas

Hi
Istrudeman
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.

It will help Experts in the forum in solving/underestanding your problem in a better way.

Please follow posting guidelines and use code tags to make your post more readable.


can u please post the table structure.

Try executing the parts of the query separately starting from inner block.

It will solve your problem.

Jun 7 '07 #3

r035198x

hi,

this uses * as the reference for finding the string pieces between and after the *. The second column in select list picks data between starting point and ending point of * and third column picks the data after second *.

hope that helps!!!
note: you can work with individual pieces to understand better

Hi Saii. Can you please enable PM for a bit so I can talk to you privately about something?

Jun 7 '07 #4

Saii

done that.............................................. .......

Jun 8 '07 #5