SOQL with character range
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
asked Nov 15 '18 at 7:20
Boris GichevBoris Gichev
184
add a comment |
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
asked Nov 15 '18 at 7:20
Boris GichevBoris Gichev
184
add a comment |
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
asked Nov 15 '18 at 7:20
Boris GichevBoris Gichev
184
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
asked Nov 15 '18 at 7:20
Boris GichevBoris Gichev
184
asked Nov 15 '18 at 7:20
Boris GichevBoris Gichev
184
asked Nov 15 '18 at 7:20
Boris GichevBoris Gichev
184
asked Nov 15 '18 at 7:20
Boris GichevBoris Gichev
184
asked Nov 15 '18 at 7:20
Boris GichevBoris Gichev
184
184
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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%'
..
answered Nov 15 '18 at 7:31
Oleksandr BerehovskyiOleksandr Berehovskyi
10.2k32238
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 Berehovskyi
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
REGEXis only available in Validation Rules and Process Builder? Or did they change that?
– Adrian Larson♦
Nov 15 '18 at 14:04
|
show 4 more comments
You can actually do string comparison using greater than/less than operators.
SELECT Field__c FROM MyObject__c
WHERE Name >= 'aa'
AND Name < 'at'
answered Nov 15 '18 at 14:01
Adrian Larson♦Adrian Larson
109k19115246
how > and < works, summation of Ascii values?
– Pranay Jaiswal
Nov 15 '18 at 14:15
2
It should have the same mechanics asORDER 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
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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%'
..
answered Nov 15 '18 at 7:31
Oleksandr BerehovskyiOleksandr Berehovskyi
10.2k32238
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 Berehovskyi
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
REGEXis only available in Validation Rules and Process Builder? Or did they change that?
– Adrian Larson♦
Nov 15 '18 at 14:04
|
show 4 more comments
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%'
..
answered Nov 15 '18 at 7:31
Oleksandr BerehovskyiOleksandr Berehovskyi
10.2k32238
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 Berehovskyi
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
REGEXis only available in Validation Rules and Process Builder? Or did they change that?
– Adrian Larson♦
Nov 15 '18 at 14:04
|
show 4 more comments
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%'
..
answered Nov 15 '18 at 7:31
Oleksandr BerehovskyiOleksandr Berehovskyi
10.2k32238
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%'
..
answered Nov 15 '18 at 7:31
Oleksandr BerehovskyiOleksandr Berehovskyi
10.2k32238
edited Nov 15 '18 at 14:06
answered Nov 15 '18 at 7:31
Oleksandr BerehovskyiOleksandr Berehovskyi
10.2k32238
answered Nov 15 '18 at 7:31
Oleksandr BerehovskyiOleksandr Berehovskyi
10.2k32238
answered Nov 15 '18 at 7:31
Oleksandr BerehovskyiOleksandr Berehovskyi
10.2k32238
10.2k32238
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 Berehovskyi
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
REGEXis only available in Validation Rules and Process Builder? Or did they change that?
– Adrian Larson♦
Nov 15 '18 at 14:04
|
show 4 more comments
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 Berehovskyi
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
REGEXis 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 Berehovskyi
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 Berehovskyi
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
|
show 4 more comments
You can actually do string comparison using greater than/less than operators.
SELECT Field__c FROM MyObject__c
WHERE Name >= 'aa'
AND Name < 'at'
answered Nov 15 '18 at 14:01
Adrian Larson♦Adrian Larson
109k19115246
how > and < works, summation of Ascii values?
– Pranay Jaiswal
Nov 15 '18 at 14:15
2
It should have the same mechanics asORDER 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
add a comment |
You can actually do string comparison using greater than/less than operators.
SELECT Field__c FROM MyObject__c
WHERE Name >= 'aa'
AND Name < 'at'
answered Nov 15 '18 at 14:01
Adrian Larson♦Adrian Larson
109k19115246
how > and < works, summation of Ascii values?
– Pranay Jaiswal
Nov 15 '18 at 14:15
2
It should have the same mechanics asORDER 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
add a comment |
You can actually do string comparison using greater than/less than operators.
SELECT Field__c FROM MyObject__c
WHERE Name >= 'aa'
AND Name < 'at'
answered Nov 15 '18 at 14:01
Adrian Larson♦Adrian Larson
109k19115246
You can actually do string comparison using greater than/less than operators.
SELECT Field__c FROM MyObject__c
WHERE Name >= 'aa'
AND Name < 'at'
answered Nov 15 '18 at 14:01
Adrian Larson♦Adrian Larson
109k19115246
answered Nov 15 '18 at 14:01
Adrian Larson♦Adrian Larson
109k19115246
answered Nov 15 '18 at 14:01
Adrian Larson♦Adrian Larson
109k19115246
answered Nov 15 '18 at 14:01
Adrian Larson♦Adrian Larson
109k19115246
109k19115246
how > and < works, summation of Ascii values?
– Pranay Jaiswal
Nov 15 '18 at 14:15
2
It should have the same mechanics asORDER 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
add a comment |
how > and < works, summation of Ascii values?
– Pranay Jaiswal
Nov 15 '18 at 14:15
2
It should have the same mechanics asORDER 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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
