Using TRIM in QUERY Match











up vote
0
down vote

favorite












This is driving me nuts so I beg for assistance!



I have this query



"Select A, B where UPPER(H) matches

    '.*(?:^|,|,s)"&REGEXEXTRACT(Q3,"^[^[{]+")&"(?:,s|,|$).*' limit 1",0)


But I need to change the match so that ignore spaces. Ideally I would just wrap Q3 in a trim but don't think I can do that.



Thanks in advance






sql regex google-sheets







share|improve this question




























    up vote
    0
    down vote

    favorite












    This is driving me nuts so I beg for assistance!



    I have this query



    "Select A, B where UPPER(H) matches
    
    '.*(?:^|,|,s)"&REGEXEXTRACT(Q3,"^[^[{]+")&"(?:,s|,|$).*' limit 1",0)


    But I need to change the match so that ignore spaces. Ideally I would just wrap Q3 in a trim but don't think I can do that.



    Thanks in advance






    sql regex google-sheets







    share|improve this question


























      up vote
      0
      down vote

      favorite









      up vote
      0
      down vote

      favorite











      This is driving me nuts so I beg for assistance!



      I have this query



      "Select A, B where UPPER(H) matches
      
    '.*(?:^|,|,s)"&REGEXEXTRACT(Q3,"^[^[{]+")&"(?:,s|,|$).*' limit 1",0)


      But I need to change the match so that ignore spaces. Ideally I would just wrap Q3 in a trim but don't think I can do that.



      Thanks in advance






      sql regex google-sheets







      share|improve this question















      This is driving me nuts so I beg for assistance!



      I have this query



      "Select A, B where UPPER(H) matches
      
    '.*(?:^|,|,s)"&REGEXEXTRACT(Q3,"^[^[{]+")&"(?:,s|,|$).*' limit 1",0)


      But I need to change the match so that ignore spaces. Ideally I would just wrap Q3 in a trim but don't think I can do that.



      Thanks in advance






      sql regex google-sheets




      sql regex google-sheets






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 19 at 11:19









      Tim Biegeleisen

      209k1380129




      209k1380129










      asked Nov 19 at 11:17









      Chris Barrett

      827




      827
























          1 Answer
          1






          active

          oldest

          votes

















          up vote
          0
          down vote













          A [^[{] pattern matches any char but [ and { and thus also matches whitespace.



          You may match any amount of whitespaces at the start, then capture any amount of chars other than [, {, and then match a char other than [, { or whitespace:



          =REGEXEXTRACT(B40,"^s*([^[{]*[^[{s])")


          Details





          • ^ - start of string


          • s* - 0+ whitespaces


          • ([^[{]*[^[{s]) - Group 1:



            • [^[{]* - 0+ chars other than [ and {


            • [^[{s] - a single char other than [, { and whitespace.








          share|improve this answer





















            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














             

            draft saved


            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373492%2fusing-trim-in-query-match%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes








            up vote
            0
            down vote













            A [^[{] pattern matches any char but [ and { and thus also matches whitespace.



            You may match any amount of whitespaces at the start, then capture any amount of chars other than [, {, and then match a char other than [, { or whitespace:



            =REGEXEXTRACT(B40,"^s*([^[{]*[^[{s])")


            Details





            • ^ - start of string


            • s* - 0+ whitespaces


            • ([^[{]*[^[{s]) - Group 1:



              • [^[{]* - 0+ chars other than [ and {


              • [^[{s] - a single char other than [, { and whitespace.








            share|improve this answer

























              up vote
              0
              down vote













              A [^[{] pattern matches any char but [ and { and thus also matches whitespace.



              You may match any amount of whitespaces at the start, then capture any amount of chars other than [, {, and then match a char other than [, { or whitespace:



              =REGEXEXTRACT(B40,"^s*([^[{]*[^[{s])")


              Details





              • ^ - start of string


              • s* - 0+ whitespaces


              • ([^[{]*[^[{s]) - Group 1:



                • [^[{]* - 0+ chars other than [ and {


                • [^[{s] - a single char other than [, { and whitespace.








              share|improve this answer























                up vote
                0
                down vote










                up vote
                0
                down vote









                A [^[{] pattern matches any char but [ and { and thus also matches whitespace.



                You may match any amount of whitespaces at the start, then capture any amount of chars other than [, {, and then match a char other than [, { or whitespace:



                =REGEXEXTRACT(B40,"^s*([^[{]*[^[{s])")


                Details





                • ^ - start of string


                • s* - 0+ whitespaces


                • ([^[{]*[^[{s]) - Group 1:



                  • [^[{]* - 0+ chars other than [ and {


                  • [^[{s] - a single char other than [, { and whitespace.








                share|improve this answer












                A [^[{] pattern matches any char but [ and { and thus also matches whitespace.



                You may match any amount of whitespaces at the start, then capture any amount of chars other than [, {, and then match a char other than [, { or whitespace:



                =REGEXEXTRACT(B40,"^s*([^[{]*[^[{s])")


                Details





                • ^ - start of string


                • s* - 0+ whitespaces


                • ([^[{]*[^[{s]) - Group 1:



                  • [^[{]* - 0+ chars other than [ and {


                  • [^[{s] - a single char other than [, { and whitespace.









                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Nov 19 at 11:32









                Wiktor Stribiżew

                301k16122197




                301k16122197






























                     

                    draft saved


                    draft discarded



















































                     


                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373492%2fusing-trim-in-query-match%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    -

                    Popular posts from this blog

                    404 Error Contact Form 7 ajax form submitting

                    Image
                    0 The contact form does not work, and affects loading after one try. The chrome console shows the error message: POST http://example.com/wp-json/contact-form-7/v1/contact-forms/50/feedback 404 (Not Found) send @ jquery.js?ver=1.12.4:4 ajax @ jquery.js?ver=1.12.4:4 wpcf7.submit @ scripts.js?ver=5.0.3:346 (anonymous) @ scripts.js?ver=5.0.3:53 dispatch @ jquery.js?ver=1.12.4:3 r.handle @ jquery.js?ver=1.12.4:3 How to solve this issue? thanks in advance javascript wordpress contact-form-7 share | improve this question edited Jul 17 '18 at 5:39 Kiran Shahi 4,745 3 16 43 ...
                    Read more

                    How to know if a Active Directory user can login interactively

                    Image
                    1 I would like to know if and how is it possible to know if a AD user can login interactively (on a server) in a domain. I need to know if I can find it out using a LDAP search. Thanks. windows active-directory login user-permissions user-profile share | improve this question edited 1 hour ago yagmoth555 ♦ 11.6k 3 17 42 asked 1 hour ago Luigi Luigi 6 1 New contributor ...
                    Read more

                    Refactoring coordinates for Minecraft Pi buildings written in Python

                    Image
                    0 $begingroup$ I'm fairly new to coding and I wrote this script in Python for a house with a roof in Minecraft Pi. I've found different ways to write the coordinates to make the code base shorter, but I'm wondering if it is possible for me to refactor it any further so I don't have to list each coordinate line by line. import mcpi.minecraft as minecraft import mcpi.block as block mc = minecraft.Minecraft.create() p = mc.player.getTilePos() mc.setBlocks(p.x+1, p.y, p.z+1, #Main structure starting coordinates (DO NOT ALTER) p.x+19, p.y+4, p.z+6, #Structure size/coordinates (Alters cube size) block.STONE_BRICK) mc.setBlocks(p.x+2, p.y+1, p.z+2, #Hollowing out main structure interior p.x+18, p.y+3, p.z+5, block.AIR) mc.setBlocks(p.x, p.y...
                    Read more