SOQL with character range
Clash Royale CLAN TAG#URR8PPP
.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 yesterday
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
yesterday
@BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
– Oleksandr Berehovskiy
yesterday
Yes I read this, but thought that I am missing something. Ok, thank you Olek.
– Boris Gichev
yesterday
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
yesterday
2
REGEX
is only available in Validation Rules and Process Builder? Or did they change that?
– Adrian Larson♦
yesterday
|
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
yesterday
2
It should have the same mechanics asORDER BY
.
– Adrian Larson♦
yesterday
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
14 hours 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
yesterday
@BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
– Oleksandr Berehovskiy
yesterday
Yes I read this, but thought that I am missing something. Ok, thank you Olek.
– Boris Gichev
yesterday
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
yesterday
2
REGEX
is only available in Validation Rules and Process Builder? Or did they change that?
– Adrian Larson♦
yesterday
|
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
yesterday
@BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
– Oleksandr Berehovskiy
yesterday
Yes I read this, but thought that I am missing something. Ok, thank you Olek.
– Boris Gichev
yesterday
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
yesterday
2
REGEX
is only available in Validation Rules and Process Builder? Or did they change that?
– Adrian Larson♦
yesterday
|
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 yesterday
answered yesterday
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
yesterday
@BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
– Oleksandr Berehovskiy
yesterday
Yes I read this, but thought that I am missing something. Ok, thank you Olek.
– Boris Gichev
yesterday
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
yesterday
2
REGEX
is only available in Validation Rules and Process Builder? Or did they change that?
– Adrian Larson♦
yesterday
|
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
yesterday
@BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
– Oleksandr Berehovskiy
yesterday
Yes I read this, but thought that I am missing something. Ok, thank you Olek.
– Boris Gichev
yesterday
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
yesterday
2
REGEX
is only available in Validation Rules and Process Builder? Or did they change that?
– Adrian Larson♦
yesterday
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
yesterday
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
yesterday
@BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
– Oleksandr Berehovskiy
yesterday
@BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator
– Oleksandr Berehovskiy
yesterday
Yes I read this, but thought that I am missing something. Ok, thank you Olek.
– Boris Gichev
yesterday
Yes I read this, but thought that I am missing something. Ok, thank you Olek.
– Boris Gichev
yesterday
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
yesterday
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
yesterday
2
2
REGEX
is only available in Validation Rules and Process Builder? Or did they change that?– Adrian Larson♦
yesterday
REGEX
is only available in Validation Rules and Process Builder? Or did they change that?– Adrian Larson♦
yesterday
|
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
yesterday
2
It should have the same mechanics asORDER BY
.
– Adrian Larson♦
yesterday
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
14 hours 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
yesterday
2
It should have the same mechanics asORDER BY
.
– Adrian Larson♦
yesterday
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
14 hours 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 yesterday
Adrian Larson♦
102k19110232
102k19110232
how > and < works, summation of Ascii values?
– Pranay Jaiswal
yesterday
2
It should have the same mechanics asORDER BY
.
– Adrian Larson♦
yesterday
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
14 hours ago
add a comment |
how > and < works, summation of Ascii values?
– Pranay Jaiswal
yesterday
2
It should have the same mechanics asORDER BY
.
– Adrian Larson♦
yesterday
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
14 hours ago
how > and < works, summation of Ascii values?
– Pranay Jaiswal
yesterday
how > and < works, summation of Ascii values?
– Pranay Jaiswal
yesterday
2
2
It should have the same mechanics as
ORDER BY
.– Adrian Larson♦
yesterday
It should have the same mechanics as
ORDER BY
.– Adrian Larson♦
yesterday
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
14 hours 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
14 hours 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