SOQL with character range












3














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



























    3














    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

























      3












      3








      3







      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













      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




      soql






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 15 '18 at 7:20









      Boris GichevBoris Gichev

      184




      184






















          2 Answers
          2






          active

          oldest

          votes


















          4














          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 '18 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 '18 at 7:59










          • Yes I read this, but thought that I am missing something. Ok, thank you Olek.
            – Boris Gichev
            Nov 15 '18 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 '18 at 8:14






          • 2




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



















          4














          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 '18 at 14:15






          • 2




            It should have the same mechanics as ORDER BY.
            – Adrian Larson♦
            Nov 15 '18 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
            Nov 16 '18 at 15:12











          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',
          autoActivateHeartbeat: false,
          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
          });


          }
          });














          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









          4














          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 '18 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 '18 at 7:59










          • Yes I read this, but thought that I am missing something. Ok, thank you Olek.
            – Boris Gichev
            Nov 15 '18 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 '18 at 8:14






          • 2




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
















          4














          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 '18 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 '18 at 7:59










          • Yes I read this, but thought that I am missing something. Ok, thank you Olek.
            – Boris Gichev
            Nov 15 '18 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 '18 at 8:14






          • 2




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














          4












          4








          4






          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 '18 at 14:06

























          answered Nov 15 '18 at 7:31









          Oleksandr BerehovskiyOleksandr Berehovskiy

          9,31932038




          9,31932038












          • 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 '18 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 '18 at 7:59










          • Yes I read this, but thought that I am missing something. Ok, thank you Olek.
            – Boris Gichev
            Nov 15 '18 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 '18 at 8:14






          • 2




            REGEX is only available in Validation Rules and Process Builder? Or did they change that?
            – Adrian Larson♦
            Nov 15 '18 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 '18 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 '18 at 7:59










          • Yes I read this, but thought that I am missing something. Ok, thank you Olek.
            – Boris Gichev
            Nov 15 '18 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 '18 at 8:14






          • 2




            REGEX is only available in Validation Rules and Process Builder? Or did they change that?
            – Adrian Larson♦
            Nov 15 '18 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 '18 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 '18 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 '18 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 '18 at 7:59












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




          Yes I read this, but thought that I am missing something. Ok, thank you Olek.
          – Boris Gichev
          Nov 15 '18 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 '18 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 '18 at 8:14




          2




          2




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




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













          4














          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 '18 at 14:15






          • 2




            It should have the same mechanics as ORDER BY.
            – Adrian Larson♦
            Nov 15 '18 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
            Nov 16 '18 at 15:12
















          4














          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 '18 at 14:15






          • 2




            It should have the same mechanics as ORDER BY.
            – Adrian Larson♦
            Nov 15 '18 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
            Nov 16 '18 at 15:12














          4












          4








          4






          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 '18 at 14:01









          Adrian Larson♦Adrian Larson

          105k19112236




          105k19112236












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






          • 2




            It should have the same mechanics as ORDER BY.
            – Adrian Larson♦
            Nov 15 '18 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
            Nov 16 '18 at 15:12


















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






          • 2




            It should have the same mechanics as ORDER BY.
            – Adrian Larson♦
            Nov 15 '18 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
            Nov 16 '18 at 15:12
















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




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




          2




          2




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






          It should have the same mechanics as ORDER BY.
          – Adrian Larson♦
          Nov 15 '18 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
          Nov 16 '18 at 15:12




          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
          Nov 16 '18 at 15:12


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Salesforce Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid …



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.





          Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


          Please pay close attention to the following guidance:


          • Please be sure to answer the question. Provide details and share your research!

          But avoid …



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          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

          Guess what letter conforming each word

          Image
          6 $begingroup$ This is an entry in the Fortnightly Topic Challenge #41: Short and Sweet. 1 letter only could be mean anything . With an additional letter in front , could be used as medicine component and something you definitely learn or at least heard once. Add another letter in front , you would possibly hear that many times . Add an additional letter in front , you would found an independent country . With an additional letter, in the end , you would found something related to apple . Add another letter in the end , you would found something bad . Hint: Each letter is unique . word knowledge letters chemistry share | improve this question ...
          Read more

          Port of Spain

          Image
          For ports in the country of Spain, see List of ports in Spain. City in City of Port of Spain, Trinidad and Tobago Port of Spain City City of Port of Spain Top to bottom (left to right): Skyline of Port of Spain from Gulf of Paria; National Academy for the Performing Arts, Independence Square, Eric Williams Plaza; MovieTowne, Port of Port of Spain Motto(s):  "We Gather Strength As We Go Along" Port of Spain Coordinates: 10°40′N 61°31′W  /  10.667°N 61.517°W  / 10.667; -61.517 Coordinates: 10°40′N 61°31′W  /  10.667°N 61.517°W  / 10.667; -61.517 Country   Trinidad and Tobago Jurisdiction City of Port of Spain Settled 1560 Incorporated (town) 1853 Incorporated (city) 1914 Government  • Mayor Joel Martinez  • Governing body City Corporation Area [1]  • City 12 km 2 (5 sq mi) Elevation [2] 66 m (217 ft) Population (2011) [1]  • City 37,074  • Rank 3rd  • Density 3,090/km 2 (8,000/sq mi)...
          Read more

          Run scheduled task as local user group (not BUILTIN)

          Image
          0 I am trying to run a scheduled task with User Group (not a single user). This is working fine when I in the field "when running a task, use the following user account:" insert BUILTIN User Group (for example: Administrators or Users). But when I try it with the created Local User Group (example: TestGroup) it is not working. The Users in the TestGroup are the same as in the Administrators group. What could be the problem? Are there some restrictions to the Created User Group compared to BUILTINUsers? scheduled-tasks taskscheduler windowstaskschedule share | improve this question asked Nov 15 '18 at 15:36 Far...
          Read more