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*07F A (or 1234567*FPER*07 FA). 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_20 07_ID, 1,7) as student_id
,substr(a.TA_20 07_ID,INSTR(a.T A_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_20 07_ID,INSTR(a.T A_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
3 2621 Motoma 3,237
Recognized Expert Specialist
I think this link should be able to explain everything that is going on in that query.
Come back if you need more help.
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 3,237
Recognized Expert Specialist
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: DTB |
last post by:
I am trying to convert a complex function from Oracle to SQL Server
and have come across Oracle's Instr() function. I see SQL Server has
CHARINDEX() which is similar, however it does not provide some key
functionality I need. Here is an example of the Oracle code:
if Instr( sTg , cDelim, 1, 3 ) > 0 then
sd := SubStr( sTg, Instr( sTg , cDelim, 1, 1 ) + 1, Instr( sTg,
cDelim, 1, 2 ) - Instr( sTg , cDelim, 1, 1 ) - 1)
|
by: drdeadpan |
last post by:
I asked the DBA to start the Server with options -T1204 and -T3605 and
here's what I get. I need help deciphering this. This happens when we
have 5 usrs performing concurrent actions and for the life of me cannot
decipher this. WHat is a Node, what is the branch ?
WHat are the ECIDs. I cannot even easily find out what process blocks
what. Does anyone have any experience with these. ANy and all help will
be appreaciated. I have posted the...
|
by: cloudx |
last post by:
hi there, how do I translate the following vb code to C#? I know to use
Indexof, but what would be for """"? Thanks!
InStr(1, sDocName, """")
|
by: PJSimon |
last post by:
Let's say I want to find the occurance of SearchString in MyString, but I
want to search without regard to case ...
Which is better and why in VB.Net?
Index = Instr(MyString, SearchString) - 1
or
Index = MyString.ToUpper.IndexOf(SearchString.ToUpper)
|
by: brad |
last post by:
Hello all, I'm new to javascript--not too new to a few other
programming languages--and I need your help deciphering the Regexp in
the following string. Regular expresions are hard enough in Python, and
since I am new to javascript they are even harder. Well here's the
string, thanks for any and all help I receive.
document.URL.match(/^(.+?)(?:\?(?:(.*?)@)?(.+))?$/)
| |
by: fischerspooner |
last post by:
Hi,
I'm banging my head against the desk because I can't find a solution for the following simple problem.
Case: There is a column in a table that has FamilyName and FirstName(s) in one field. ie "McCarthy,John Doe Willy". I have to split up Familyname and the 1st FirstName. The FamilyName works perfect, just looking up the comma with InStr and use Left().
Now can someone throw me a bone to help me get the 1st Firstname. "John" according...
|
by: rodchar |
last post by:
hey all,
i'm getting a result that i don't understand
i have a string "test1, test2"
If InStr("test1") and InStr("test2") Then
'Inside
EndIf
The inside is not running for some reason. Any ideas?
|
by: Alex Pavluck |
last post by:
I have a date stored like this '2004 - 2006' and I use this code to
get startyear and stopyear
StartYear: Trim(Left(,InStr(,"-")-1))
StopYear: Trim(Right(,InStr(,"-")-1))
Is there a way to get this to only run on NON MISSING Project Dates?
Thanks!
Alex
|
by: colinod |
last post by:
I have a page that uses a session variable that stores items like a shopping cart, all this works fine but i am trying to code a button that removes an item, i have a page that i have set up for the remove function and have tried the following code
dim firstHalf, secondHalf
firstHalf = left(session("recordsInCart"), instr(request.form("recordNum")))
secondHalf = right(session("recordsInCart"), len(session("recordsInCart")) -...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |