SOQL with character range





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty{ margin-bottom:0;
}






up vote
3
down vote

favorite












I am trying to get all records whose name starts with "a"
and the second letter is in the range [a-s]



The quires I tried but no luck:



FROM 
Agreement__c
WHERE
Account__r.FirstName LIKE 'a[a-s]%'


or



FROM 
Agreement__c
WHERE
Account__r.FirstName LIKE 'a%'
AND Account__r.FirstName LIKE '_[a-s]%'


It is very strange because this works as expected



    FROM 
Agreement__c
WHERE
Account__r.FirstName LIKE 'a%'
AND Account__r.FirstName LIKE '_s%'


but every time I use range even like that no luck ...



Account__r.FirstName LIKE '[a-s]%'









share|improve this question







New contributor




Boris Gichev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.


























    up vote
    3
    down vote

    favorite












    I am trying to get all records whose name starts with "a"
    and the second letter is in the range [a-s]



    The quires I tried but no luck:



    FROM 
    Agreement__c
    WHERE
    Account__r.FirstName LIKE 'a[a-s]%'


    or



    FROM 
    Agreement__c
    WHERE
    Account__r.FirstName LIKE 'a%'
    AND Account__r.FirstName LIKE '_[a-s]%'


    It is very strange because this works as expected



        FROM 
    Agreement__c
    WHERE
    Account__r.FirstName LIKE 'a%'
    AND Account__r.FirstName LIKE '_s%'


    but every time I use range even like that no luck ...



    Account__r.FirstName LIKE '[a-s]%'









    share|improve this question







    New contributor




    Boris Gichev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.






















      up vote
      3
      down vote

      favorite









      up vote
      3
      down vote

      favorite











      I am trying to get all records whose name starts with "a"
      and the second letter is in the range [a-s]



      The quires I tried but no luck:



      FROM 
      Agreement__c
      WHERE
      Account__r.FirstName LIKE 'a[a-s]%'


      or



      FROM 
      Agreement__c
      WHERE
      Account__r.FirstName LIKE 'a%'
      AND Account__r.FirstName LIKE '_[a-s]%'


      It is very strange because this works as expected



          FROM 
      Agreement__c
      WHERE
      Account__r.FirstName LIKE 'a%'
      AND Account__r.FirstName LIKE '_s%'


      but every time I use range even like that no luck ...



      Account__r.FirstName LIKE '[a-s]%'









      share|improve this question







      New contributor




      Boris Gichev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      I am trying to get all records whose name starts with "a"
      and the second letter is in the range [a-s]



      The quires I tried but no luck:



      FROM 
      Agreement__c
      WHERE
      Account__r.FirstName LIKE 'a[a-s]%'


      or



      FROM 
      Agreement__c
      WHERE
      Account__r.FirstName LIKE 'a%'
      AND Account__r.FirstName LIKE '_[a-s]%'


      It is very strange because this works as expected



          FROM 
      Agreement__c
      WHERE
      Account__r.FirstName LIKE 'a%'
      AND Account__r.FirstName LIKE '_s%'


      but every time I use range even like that no luck ...



      Account__r.FirstName LIKE '[a-s]%'






      soql






      share|improve this question







      New contributor




      Boris Gichev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      share|improve this question







      New contributor




      Boris Gichev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this question




      share|improve this question






      New contributor




      Boris Gichev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked Nov 15 at 7:20









      Boris Gichev

      183




      183




      New contributor




      Boris Gichev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      Boris Gichev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      Boris Gichev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






















          2 Answers
          2






          active

          oldest

          votes

















          up vote
          4
          down vote



          accepted










          regex expressions are not supported in SOQL queries.






          It is very strange because this works as expected



          FROM 
          Agreement__c WHERE Account__r.FirstName LIKE 'a%' AND Account__r.FirstName LIKE '_s%'



          it works as you expect, because LIKE 'a%' means field starts with a after could be zero or more any chanacters, LIKE '_s%' means query all records where FirstName starts from any exactly one character, second is s and after s zero or more any characters. Combination of two conditions gives your expected result.





          in order to achieve desired result with SOQL, you have to manually include all symbols in condition:



          WHERE 
          Account__r.FirstName LIKE 'a%'
          AND
          (
          Account__r.FirstName LIKE '_a%'
          OR Account__r.FirstName LIKE '_b%'
          OR Account__r.FirstName LIKE '_c%'
          ..





          share|improve this answer























          • Yeas that is true and that would work (even if it is the most beautiful code to see) but it is hard to believe that char ranges can not be used here !
            – Boris Gichev
            Nov 15 at 7:58












          • @BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
            – Oleksandr Berehovskiy
            Nov 15 at 7:59










          • Yes I read this, but thought that I am missing something. Ok, thank you Olek.
            – Boris Gichev
            Nov 15 at 8:02






          • 1




            Ahaa you use the reg ex via the fields, because they can use REGEX :). Nice to have that trick in mind. I will go for the first option since I am queering the objects for a document. And it would be faster now. But if I need that again I might consider creating a few filter fields in the objects that I need to query. Thank you for the efforts !
            – Boris Gichev
            Nov 15 at 8:14






          • 2




            REGEX is only available in Validation Rules and Process Builder? Or did they change that?
            – Adrian Larson
            Nov 15 at 14:04


















          up vote
          4
          down vote













          You can actually do string comparison using greater than/less than operators.



          SELECT Field__c FROM MyObject__c
          WHERE Name >= 'aa'
          AND Name < 'at'





          share|improve this answer





















          • how > and < works, summation of Ascii values?
            – Pranay Jaiswal
            Nov 15 at 14:15






          • 2




            It should have the same mechanics as ORDER BY.
            – Adrian Larson
            Nov 15 at 14:45












          • This looks good, but first I have to check the char table because the organization uses characters like öäå and that may cause unwanted behavior. Or maybe just make one dedicated filter only for those characters.
            – Boris Gichev
            2 days ago











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "459"
          };
          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: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          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
          });


          }
          });






          Boris Gichev is a new contributor. Be nice, and check out our Code of Conduct.










           

          draft saved


          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f239441%2fsoql-with-character-range%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          2 Answers
          2






          active

          oldest

          votes








          2 Answers
          2






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          up vote
          4
          down vote



          accepted










          regex expressions are not supported in SOQL queries.






          It is very strange because this works as expected



          FROM 
          Agreement__c WHERE Account__r.FirstName LIKE 'a%' AND Account__r.FirstName LIKE '_s%'



          it works as you expect, because LIKE 'a%' means field starts with a after could be zero or more any chanacters, LIKE '_s%' means query all records where FirstName starts from any exactly one character, second is s and after s zero or more any characters. Combination of two conditions gives your expected result.





          in order to achieve desired result with SOQL, you have to manually include all symbols in condition:



          WHERE 
          Account__r.FirstName LIKE 'a%'
          AND
          (
          Account__r.FirstName LIKE '_a%'
          OR Account__r.FirstName LIKE '_b%'
          OR Account__r.FirstName LIKE '_c%'
          ..





          share|improve this answer























          • Yeas that is true and that would work (even if it is the most beautiful code to see) but it is hard to believe that char ranges can not be used here !
            – Boris Gichev
            Nov 15 at 7:58












          • @BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
            – Oleksandr Berehovskiy
            Nov 15 at 7:59










          • Yes I read this, but thought that I am missing something. Ok, thank you Olek.
            – Boris Gichev
            Nov 15 at 8:02






          • 1




            Ahaa you use the reg ex via the fields, because they can use REGEX :). Nice to have that trick in mind. I will go for the first option since I am queering the objects for a document. And it would be faster now. But if I need that again I might consider creating a few filter fields in the objects that I need to query. Thank you for the efforts !
            – Boris Gichev
            Nov 15 at 8:14






          • 2




            REGEX is only available in Validation Rules and Process Builder? Or did they change that?
            – Adrian Larson
            Nov 15 at 14:04















          up vote
          4
          down vote



          accepted










          regex expressions are not supported in SOQL queries.






          It is very strange because this works as expected



          FROM 
          Agreement__c WHERE Account__r.FirstName LIKE 'a%' AND Account__r.FirstName LIKE '_s%'



          it works as you expect, because LIKE 'a%' means field starts with a after could be zero or more any chanacters, LIKE '_s%' means query all records where FirstName starts from any exactly one character, second is s and after s zero or more any characters. Combination of two conditions gives your expected result.





          in order to achieve desired result with SOQL, you have to manually include all symbols in condition:



          WHERE 
          Account__r.FirstName LIKE 'a%'
          AND
          (
          Account__r.FirstName LIKE '_a%'
          OR Account__r.FirstName LIKE '_b%'
          OR Account__r.FirstName LIKE '_c%'
          ..





          share|improve this answer























          • Yeas that is true and that would work (even if it is the most beautiful code to see) but it is hard to believe that char ranges can not be used here !
            – Boris Gichev
            Nov 15 at 7:58












          • @BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
            – Oleksandr Berehovskiy
            Nov 15 at 7:59










          • Yes I read this, but thought that I am missing something. Ok, thank you Olek.
            – Boris Gichev
            Nov 15 at 8:02






          • 1




            Ahaa you use the reg ex via the fields, because they can use REGEX :). Nice to have that trick in mind. I will go for the first option since I am queering the objects for a document. And it would be faster now. But if I need that again I might consider creating a few filter fields in the objects that I need to query. Thank you for the efforts !
            – Boris Gichev
            Nov 15 at 8:14






          • 2




            REGEX is only available in Validation Rules and Process Builder? Or did they change that?
            – Adrian Larson
            Nov 15 at 14:04













          up vote
          4
          down vote



          accepted







          up vote
          4
          down vote



          accepted






          regex expressions are not supported in SOQL queries.






          It is very strange because this works as expected



          FROM 
          Agreement__c WHERE Account__r.FirstName LIKE 'a%' AND Account__r.FirstName LIKE '_s%'



          it works as you expect, because LIKE 'a%' means field starts with a after could be zero or more any chanacters, LIKE '_s%' means query all records where FirstName starts from any exactly one character, second is s and after s zero or more any characters. Combination of two conditions gives your expected result.





          in order to achieve desired result with SOQL, you have to manually include all symbols in condition:



          WHERE 
          Account__r.FirstName LIKE 'a%'
          AND
          (
          Account__r.FirstName LIKE '_a%'
          OR Account__r.FirstName LIKE '_b%'
          OR Account__r.FirstName LIKE '_c%'
          ..





          share|improve this answer














          regex expressions are not supported in SOQL queries.






          It is very strange because this works as expected



          FROM 
          Agreement__c WHERE Account__r.FirstName LIKE 'a%' AND Account__r.FirstName LIKE '_s%'



          it works as you expect, because LIKE 'a%' means field starts with a after could be zero or more any chanacters, LIKE '_s%' means query all records where FirstName starts from any exactly one character, second is s and after s zero or more any characters. Combination of two conditions gives your expected result.





          in order to achieve desired result with SOQL, you have to manually include all symbols in condition:



          WHERE 
          Account__r.FirstName LIKE 'a%'
          AND
          (
          Account__r.FirstName LIKE '_a%'
          OR Account__r.FirstName LIKE '_b%'
          OR Account__r.FirstName LIKE '_c%'
          ..






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 15 at 14:06

























          answered Nov 15 at 7:31









          Oleksandr Berehovskiy

          9,06131937




          9,06131937












          • Yeas that is true and that would work (even if it is the most beautiful code to see) but it is hard to believe that char ranges can not be used here !
            – Boris Gichev
            Nov 15 at 7:58












          • @BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
            – Oleksandr Berehovskiy
            Nov 15 at 7:59










          • Yes I read this, but thought that I am missing something. Ok, thank you Olek.
            – Boris Gichev
            Nov 15 at 8:02






          • 1




            Ahaa you use the reg ex via the fields, because they can use REGEX :). Nice to have that trick in mind. I will go for the first option since I am queering the objects for a document. And it would be faster now. But if I need that again I might consider creating a few filter fields in the objects that I need to query. Thank you for the efforts !
            – Boris Gichev
            Nov 15 at 8:14






          • 2




            REGEX is only available in Validation Rules and Process Builder? Or did they change that?
            – Adrian Larson
            Nov 15 at 14:04


















          • Yeas that is true and that would work (even if it is the most beautiful code to see) but it is hard to believe that char ranges can not be used here !
            – Boris Gichev
            Nov 15 at 7:58












          • @BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
            – Oleksandr Berehovskiy
            Nov 15 at 7:59










          • Yes I read this, but thought that I am missing something. Ok, thank you Olek.
            – Boris Gichev
            Nov 15 at 8:02






          • 1




            Ahaa you use the reg ex via the fields, because they can use REGEX :). Nice to have that trick in mind. I will go for the first option since I am queering the objects for a document. And it would be faster now. But if I need that again I might consider creating a few filter fields in the objects that I need to query. Thank you for the efforts !
            – Boris Gichev
            Nov 15 at 8:14






          • 2




            REGEX is only available in Validation Rules and Process Builder? Or did they change that?
            – Adrian Larson
            Nov 15 at 14:04
















          Yeas that is true and that would work (even if it is the most beautiful code to see) but it is hard to believe that char ranges can not be used here !
          – Boris Gichev
          Nov 15 at 7:58






          Yeas that is true and that would work (even if it is the most beautiful code to see) but it is hard to believe that char ranges can not be used here !
          – Boris Gichev
          Nov 15 at 7:58














          @BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
          – Oleksandr Berehovskiy
          Nov 15 at 7:59




          @BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
          – Oleksandr Berehovskiy
          Nov 15 at 7:59












          Yes I read this, but thought that I am missing something. Ok, thank you Olek.
          – Boris Gichev
          Nov 15 at 8:02




          Yes I read this, but thought that I am missing something. Ok, thank you Olek.
          – Boris Gichev
          Nov 15 at 8:02




          1




          1




          Ahaa you use the reg ex via the fields, because they can use REGEX :). Nice to have that trick in mind. I will go for the first option since I am queering the objects for a document. And it would be faster now. But if I need that again I might consider creating a few filter fields in the objects that I need to query. Thank you for the efforts !
          – Boris Gichev
          Nov 15 at 8:14




          Ahaa you use the reg ex via the fields, because they can use REGEX :). Nice to have that trick in mind. I will go for the first option since I am queering the objects for a document. And it would be faster now. But if I need that again I might consider creating a few filter fields in the objects that I need to query. Thank you for the efforts !
          – Boris Gichev
          Nov 15 at 8:14




          2




          2




          REGEX is only available in Validation Rules and Process Builder? Or did they change that?
          – Adrian Larson
          Nov 15 at 14:04




          REGEX is only available in Validation Rules and Process Builder? Or did they change that?
          – Adrian Larson
          Nov 15 at 14:04












          up vote
          4
          down vote













          You can actually do string comparison using greater than/less than operators.



          SELECT Field__c FROM MyObject__c
          WHERE Name >= 'aa'
          AND Name < 'at'





          share|improve this answer





















          • how > and < works, summation of Ascii values?
            – Pranay Jaiswal
            Nov 15 at 14:15






          • 2




            It should have the same mechanics as ORDER BY.
            – Adrian Larson
            Nov 15 at 14:45












          • This looks good, but first I have to check the char table because the organization uses characters like öäå and that may cause unwanted behavior. Or maybe just make one dedicated filter only for those characters.
            – Boris Gichev
            2 days ago















          up vote
          4
          down vote













          You can actually do string comparison using greater than/less than operators.



          SELECT Field__c FROM MyObject__c
          WHERE Name >= 'aa'
          AND Name < 'at'





          share|improve this answer





















          • how > and < works, summation of Ascii values?
            – Pranay Jaiswal
            Nov 15 at 14:15






          • 2




            It should have the same mechanics as ORDER BY.
            – Adrian Larson
            Nov 15 at 14:45












          • This looks good, but first I have to check the char table because the organization uses characters like öäå and that may cause unwanted behavior. Or maybe just make one dedicated filter only for those characters.
            – Boris Gichev
            2 days ago













          up vote
          4
          down vote










          up vote
          4
          down vote









          You can actually do string comparison using greater than/less than operators.



          SELECT Field__c FROM MyObject__c
          WHERE Name >= 'aa'
          AND Name < 'at'





          share|improve this answer












          You can actually do string comparison using greater than/less than operators.



          SELECT Field__c FROM MyObject__c
          WHERE Name >= 'aa'
          AND Name < 'at'






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 15 at 14:01









          Adrian Larson

          102k19110232




          102k19110232












          • how > and < works, summation of Ascii values?
            – Pranay Jaiswal
            Nov 15 at 14:15






          • 2




            It should have the same mechanics as ORDER BY.
            – Adrian Larson
            Nov 15 at 14:45












          • This looks good, but first I have to check the char table because the organization uses characters like öäå and that may cause unwanted behavior. Or maybe just make one dedicated filter only for those characters.
            – Boris Gichev
            2 days ago


















          • how > and < works, summation of Ascii values?
            – Pranay Jaiswal
            Nov 15 at 14:15






          • 2




            It should have the same mechanics as ORDER BY.
            – Adrian Larson
            Nov 15 at 14:45












          • This looks good, but first I have to check the char table because the organization uses characters like öäå and that may cause unwanted behavior. Or maybe just make one dedicated filter only for those characters.
            – Boris Gichev
            2 days ago
















          how > and < works, summation of Ascii values?
          – Pranay Jaiswal
          Nov 15 at 14:15




          how > and < works, summation of Ascii values?
          – Pranay Jaiswal
          Nov 15 at 14:15




          2




          2




          It should have the same mechanics as ORDER BY.
          – Adrian Larson
          Nov 15 at 14:45






          It should have the same mechanics as ORDER BY.
          – Adrian Larson
          Nov 15 at 14:45














          This looks good, but first I have to check the char table because the organization uses characters like öäå and that may cause unwanted behavior. Or maybe just make one dedicated filter only for those characters.
          – Boris Gichev
          2 days ago




          This looks good, but first I have to check the char table because the organization uses characters like öäå and that may cause unwanted behavior. Or maybe just make one dedicated filter only for those characters.
          – Boris Gichev
          2 days ago










          Boris Gichev is a new contributor. Be nice, and check out our Code of Conduct.










           

          draft saved


          draft discarded


















          Boris Gichev is a new contributor. Be nice, and check out our Code of Conduct.













          Boris Gichev is a new contributor. Be nice, and check out our Code of Conduct.












          Boris Gichev is a new contributor. Be nice, and check out our Code of Conduct.















           


          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f239441%2fsoql-with-character-range%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

          AnyDesk - Fatal Program Failure

          How to calibrate 16:9 built-in touch-screen to a 4:3 resolution?

          QoS: MAC-Priority for clients behind a repeater