By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,727 Members | 768 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,727 IT Pros & Developers. It's quick & easy.

INST Command and help breaking apart Syntax (SQL/Oracle

P: 3
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
Share this Question
Share on Google+
4 Replies


Saii
Expert 100+
P: 145
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
Expert 5K+
P: 8,127
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

10K+
P: 13,264
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
Expert 100+
P: 145
done that.............................................. .......
Jun 8 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.