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

[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

[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.

[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?

[Saii]

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