Usually with a variable character length field, you have to parse for a null space or a data separator (like a comma) at the end of the substring you want to extract. In other languages, this is most commonly done by separating each character into an array and then looping through each character in the array. I'm not sure how you'd do that with SQL alone.
Announcement
Collapse
No announcement yet.
computer issues: part infinity
Collapse
X
-
Originally posted by St. Clown View PostAny SQL query pros here? I’m trying to parse SWIFT message file using MS SQL Management Studio and need to extract a variable length field.
example text:
:20C::SEME//ABC123XX
:23G:NEWM
The 20C message tag is variable length, and while I know how to start the field extract in the right spot, I can’t nail down a how to stop the the extract based upon the :23G: location.
Any help around here? It’s be much appreciated.
(Also, these SWIFT file layouts are easily available on the web, so no corp secrets revealed.)
Code:assuming the data is in a column called fld, then it's select substring(fld, charindex(':20C:', fld) + 4, charindex(':23G:', fld) - charindex(':20C:', fld) - 4)
- 1 like
Comment
-
Originally posted by TalonsUpPuckDown View Post
I reached out to one of the architects on my current team. Not sure if this will help, here's what he came up with. It assumes the tags are consistent and are not duplicated within the message.
Code:assuming the data is in a column called fld, then it's select substring(fld, charindex(':20C:', fld) + 4, charindex(':23G:', fld) - charindex(':20C:', fld) - 4)
"The party told you to reject the evidence of your eyes and ears. It was their final, most essential command." George Orwell, 1984
"One does not simply walk into Mordor. Its Black Gates are guarded by more than just Orcs. There is evil there that does not sleep, and the Great Eye is ever watchful. It is a barren wasteland, riddled with fire and ash and dust, the very air you breathe is a poisonous fume." Boromir
"Good news! We have a delivery." Professor Farnsworth
Comment
-
This site rulesCode:As of 9/21/10: As of 9/13/10: College Hockey 6 College Football 0 BTHC 4 WCHA FC: 1
Originally posted by SanTropezMay your paint thinner run dry and the fleas of a thousand camels infest your dead deer.Originally posted by bigblue_dlI don't even know how to classify magic vagina smoke babies..Originally posted by KeplerWhen the giraffes start building radio telescopes they can join too.
- 1 like
Comment
-
Originally posted by St. Clown View PostThank you very much for that!
=MID(A1,FIND(":20C:",A1)+5,FIND(":23G:",A1)-5)
Comment
-
Originally posted by TalonsUpPuckDown View PostGlad it worked out. I was reviewing this with the architect and pointed out that his solution matched very closely with stuff I'd coded in Excel to do the same thing. I am not SQL literate at all but due to being a coder two lifetimes ago (ala the 1980's pre-object oriented world of Turbo Pascal, COBOL, RPG and other dead, Latin-like, procedural-based languages and hierarchical databases) I'm quite good with Excel. After I pointed out the similarities between his syntax and Excel and he commented that it's all Microsoft so under the covers it'll all be basically the same. Duh. I pass this observation along on the off chance it may be useful in the future. Anyway, here it is in Excel assuming the message string is in cell A1:
=MID(A1,FIND(":20C:",A1)+5,FIND(":23G:",A1)-5)
they don't have a great database of recipes and I came to find out that one product might have not a single recipe but some runs use corrections to meet spec. Other runs are making the product but dumping it into a tote where they already have product, so you might have odd lot sizes or bad ratios. They also have not run variability analyses to work out inefficiencies and quality. (Ugh, I know)
The last two weeks I've been pushing excel to its absolute 32-bit limit. To the point that I've had to get used to saving every five minutes and creating a new file every 20-30 minutes just so I don't lose work. I can't tell you how many times it's crashed because it's hit memory issues. 80% of it is run through macros but it still takes about 10-15 minutes to run on the biggest product lines.
I know I should be running this stuff through a proper database with proper lookups, but I just know excel too well. Prior to this, the most complicated spreadsheet I had done was a massive KRACH and PWR spreadsheet using the old methodology for PWR. Still the most complicated single formula I've written. Super long even when pasted in word.Last edited by dxmnkd316; 05-27-2022, 07:28 AM.Code:As of 9/21/10: As of 9/13/10: College Hockey 6 College Football 0 BTHC 4 WCHA FC: 1
Originally posted by SanTropezMay your paint thinner run dry and the fleas of a thousand camels infest your dead deer.Originally posted by bigblue_dlI don't even know how to classify magic vagina smoke babies..Originally posted by KeplerWhen the giraffes start building radio telescopes they can join too.
Comment
-
Originally posted by dxmnkd316 View Post
I like to think of myself as an advanced excel user. I've been on a project recently where I was asked to crunch the data from a year's worth of product runs at a plant we're rebuilding. They have something like 4,000 SKU. Each with about 6-10 ingredients, some of which are the SKUs they make. Made on around 15-20 machines.
they don't have a great database of recipes and I came to find out that one product might have not a single recipe but some runs use corrections to meet spec. They also have not run variability analyses to work out inefficiencies and quality. (Ugh, I know)
The last two weeks I've been pushing excel to its absolute 32-bit limit. To the point that I've had to get used to saving every five minutes and creating a new file every 20-30 minutes just so I don't lose work. I can't tell you how many times it's crashed because it's hit memory issues. 80% of it is run through macros but it still takes about 10-15 minutes to run on the biggest product lines.
I know I should be running this stuff through a proper database with proper lookups, but I just know excel too well. Prior to this, the most complicated spreadsheet I had done was a massive KRACH and PWR spreadsheet using the old methodology for PWR. Still the most complicated single formula I've written. Super long even when pasted in word.If you don't change the world today, how can it be any better tomorrow?
Comment
-
Honestly, excel is the best because it's so malleable. Databases and front ends, especially SAP, treat data like R. Lee Ermey did grunts at basic. If it's not perfect, SAP is going to ruin your ****ing day (if you're lucky)
Excel might choke on the quantity of data, but it at least lets you know.Code:As of 9/21/10: As of 9/13/10: College Hockey 6 College Football 0 BTHC 4 WCHA FC: 1
Originally posted by SanTropezMay your paint thinner run dry and the fleas of a thousand camels infest your dead deer.Originally posted by bigblue_dlI don't even know how to classify magic vagina smoke babies..Originally posted by KeplerWhen the giraffes start building radio telescopes they can join too.
Comment
-
I woke up this morning to find my computer will not turn on at all.
With 2 weeks to go in the quarter for school, I'm hoping it's not fried.Facebook: bcowles920 Instagram: missthundercat01
"One word frees us from the weight and pain of this life. That word is love."- Socrates
Patreon for exclusive writing content
Adventures With Amber Marie
Comment
-
No idea where else to put this.
My mom has had a couple of falls in the last year and while she's still very independent and able to take care of herself, I'd like to take out a bit of an insurance plan. After doing some research, I'm thinking the Apple Watch is what I'm looking for. The fall detection and emergency calls seems to be pretty well thought out. I'm less inclined to get one of the medic alert bracelets or necklaces since they're very expensive to subscribe to, not very feature rich (I'm thinking things like heart variation, etc. but she'd be interested in some of the other techy features), and I think the Apple Watch would be more likely something my mom would be willing to actually use because it doesn't carry a stigma.
She doesn't have an iPhone so we'd be looking going the Family Setup route. Does anyone have any experience with this? Wanted to get some info before we get too far down the rabbit hole.Code:As of 9/21/10: As of 9/13/10: College Hockey 6 College Football 0 BTHC 4 WCHA FC: 1
Originally posted by SanTropezMay your paint thinner run dry and the fleas of a thousand camels infest your dead deer.Originally posted by bigblue_dlI don't even know how to classify magic vagina smoke babies..Originally posted by KeplerWhen the giraffes start building radio telescopes they can join too.
Comment
-
The rare good thing. Something must have happened with my ISP because I was running Speakeasy and instead of the 350-375 I usually get for download it was at 490 down / 470 up.
Thank you, Verizon?Cornell University
National Champion 1967, 1970
ECAC Champion 1967, 1968, 1969, 1970, 1973, 1980, 1986, 1996, 1997, 2003, 2005, 2010
Ivy League Champion 1966, 1967, 1968, 1969, 1970, 1971, 1972, 1973, 1977, 1978, 1983, 1984, 1985, 1996, 1997, 2002, 2003, 2004, 2005, 2012, 2014, 2018, 2019, 2020
Comment
-
Cornell University
National Champion 1967, 1970
ECAC Champion 1967, 1968, 1969, 1970, 1973, 1980, 1986, 1996, 1997, 2003, 2005, 2010
Ivy League Champion 1966, 1967, 1968, 1969, 1970, 1971, 1972, 1973, 1977, 1978, 1983, 1984, 1985, 1996, 1997, 2002, 2003, 2004, 2005, 2012, 2014, 2018, 2019, 2020
Comment
-
I’ve got a bizarre issue I could use help with, the Wi-Fi shut off on my laptop and I’m not able to turn it back on. I’ve tried running network diagnostics but it just spins and never does anything. I’ve googled but haven’t found anything useful. It’s around 10 years old so perhaps that is part of the issue. I probably should just take it to Apple and see what they can do but figured I would check here.Originally posted by BobbyBrady
Crosby probably wouldn't even be on BC's top two lines next year
Comment
-
Originally posted by Slap Shot View PostHas anyone switched to a decentralized VPN device and if so would you recommend? The up front cost isn't terrible, and if the claimed benefits are accurate not paying a monthly fee thereafter is appealing.Cornell University
National Champion 1967, 1970
ECAC Champion 1967, 1968, 1969, 1970, 1973, 1980, 1986, 1996, 1997, 2003, 2005, 2010
Ivy League Champion 1966, 1967, 1968, 1969, 1970, 1971, 1972, 1973, 1977, 1978, 1983, 1984, 1985, 1996, 1997, 2002, 2003, 2004, 2005, 2012, 2014, 2018, 2019, 2020
Comment
Comment