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]%'
soql
New contributor
add a comment |
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]%'
soql
New contributor
add a comment |
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]%'
soql
New contributor
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
New contributor
New contributor
New contributor
asked Nov 15 at 7:20
Boris Gichev
183
183
New contributor
New contributor
add a comment |
add a comment |
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%'
..
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
|
show 4 more comments
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'
how > and < works, summation of Ascii values?
– Pranay Jaiswal
Nov 15 at 14:15
2
It should have the same mechanics asORDER 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
add a comment |
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%'
..
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
|
show 4 more comments
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%'
..
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
|
show 4 more comments
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%'
..
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%'
..
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
|
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 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
|
show 4 more comments
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'
how > and < works, summation of Ascii values?
– Pranay Jaiswal
Nov 15 at 14:15
2
It should have the same mechanics asORDER 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
add a comment |
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'
how > and < works, summation of Ascii values?
– Pranay Jaiswal
Nov 15 at 14:15
2
It should have the same mechanics asORDER 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
add a comment |
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'
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 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 asORDER 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
add a comment |
how > and < works, summation of Ascii values?
– Pranay Jaiswal
Nov 15 at 14:15
2
It should have the same mechanics asORDER 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
add a comment |
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.
Boris Gichev is a new contributor. Be nice, and check out our Code of Conduct.
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