Deciphering INSTR Code

[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

[Motoma]

I think this link should be able to explain everything that is going on in that query.

Come back if you need more help.

[lstrudeman]

Thanks... I was able to get a better understanding of the INSTR command through all your help.. I also gathered...

As you probably know, INSTR finds the starting position of a string within another string. The parameters are INSTR(char1, char2, x, y)
char1 is the string to search
char2 is the string to look for
x is position in char1 to begin the search (so your code is starting at the beginning)
y is the occurrence of char2 to find (so your code will find the first occurrence)

SUBSTR takes the string, the starting position, and the # of characters to cut out.
The first INSTR finds the position of the first *, and adds 1 to move past the * and get the first real character.
To calculate how many characters to return, find the position of the SECOND * and subtract the position of the FIRST *. That gives you the length of the middle string.

[Motoma]

I am glad you were able to find your solution, and it made me quite happy to see what you learned articulated so eloquently. Thank you for posting a response that others may learn from.

Hope to see more from you here at The Scripts.