Announcement

Collapse
No announcement yet.

computer issues: part infinity

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Comment


    • Originally posted by St. Clown View Post
      Any 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.)
      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)

      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)
        Thank you very much for that!
        "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 rules
          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 SanTropez
          May your paint thinner run dry and the fleas of a thousand camels infest your dead deer.
          Originally posted by bigblue_dl
          I don't even know how to classify magic vagina smoke babies..
          Originally posted by Kepler
          When the giraffes start building radio telescopes they can join too.
          He's probably going to be a superstar but that man has more baggage than North West

          Comment


          • Originally posted by St. Clown View Post
            Thank you very much for that!
            Glad 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)

            Comment


            • Originally posted by TalonsUpPuckDown View Post
              Glad 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)
              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. 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, 08: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 SanTropez
              May your paint thinner run dry and the fleas of a thousand camels infest your dead deer.
              Originally posted by bigblue_dl
              I don't even know how to classify magic vagina smoke babies..
              Originally posted by Kepler
              When the giraffes start building radio telescopes they can join too.
              He's probably going to be a superstar but that man has more baggage than North West

              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.
                Flashbacks....My father's last role before he retired was leading a group of ~60 people implementing SAP at 100% of his chemical company's worldwide locations, including all their sales offices - in the mid 90's, so pre-internet, practically speaking. At the time, his company had more than 100,000 products ("recipes," I guess?) for sale, not to mention all the raw materials and intermediate stages of materials to track. They studied the problem for a while and concluded that Excel was the best tool for translating the data from hundreds of different business and inventory systems into SAP. They had some spreadsheets that took days to compute - of course, now your cell phone could probably handle what they did in the blink of an eye!
                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 fucking 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 SanTropez
                  May your paint thinner run dry and the fleas of a thousand camels infest your dead deer.
                  Originally posted by bigblue_dl
                  I don't even know how to classify magic vagina smoke babies..
                  Originally posted by Kepler
                  When the giraffes start building radio telescopes they can join too.
                  He's probably going to be a superstar but that man has more baggage than North West

                  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 SanTropez
                      May your paint thinner run dry and the fleas of a thousand camels infest your dead deer.
                      Originally posted by bigblue_dl
                      I don't even know how to classify magic vagina smoke babies..
                      Originally posted by Kepler
                      When the giraffes start building radio telescopes they can join too.
                      He's probably going to be a superstar but that man has more baggage than North West

                      Comment

                      Working...
                      X