3

I'm trying to use LIKE in my dynamic SOQL with a list.

My code:

SELECT Id, Name FROM Account WHERE Name LIKE ('%somevalue%', '%testvalue%')

I can't and I don't want to use variable reference for the list so I constructed this string.

The error when I try to run the above query:

expecting a colon, found '('

And When I add a colon this is the error that I get:

System.QueryException: Only variable references are allowed in dynamic SOQL/SOSL.

How can I achieve this without using a variable reference?

The only other way I could think of is:

SELECT Id, Name FROM Account WHERE Name LIKE '%somevalue%' OR Name LIKE '%testvalue%'

With the above way, there's chance of hitting query string character limit. Is there a better way to do this?

Adrian Larson
  • 149,971
  • 38
  • 239
  • 420
d_k
  • 1,281
  • 2
  • 15
  • 36
  • 1
    And do you think will it cross 4000 character limit? BTW, are you trying like this? – Thamilhan Apr 25 '18 at 05:29
  • It's likely it can cross that limit and no I don't want to use a variable reference. – d_k Apr 25 '18 at 05:50
  • You say you can't use a list variable. Any particular reason? It does seem odd that SOQL supports a LIKE against a bound collection but there isn't direct syntax support for the same query. – Daniel Ballinger Apr 25 '18 at 10:33
  • @DanielBallinger The reason is, I'm developing a VF page where a user can enter filters (something like report filters or listview filters) and query records and so a user could use any number of fields to enter the criteria to filter records and so I can't use a list variable. – d_k Apr 25 '18 at 11:21
  • @Mr.Frodo Thanks for your reply but, I don't think this is a duplicate at all. I want to achieve this using a dynamic SOQL and I can't use a list variable. – d_k Apr 25 '18 at 11:22
  • @d_k You can create a list of String with all the words in it. Then you can pass it in dynamic SOQL as well like you pass in normal SOQL query. That would work. I have requested to reopen the question, i will write an answer after that. Below is the sample code: List<String> s = new List<String>{'Burlington%','%plc'}; String query = 'select id, name from Account where name like: s'; List<Account> a = Database.query(query); – Mr.Frodo Apr 25 '18 at 17:47

1 Answers1

2

You can create a list of String with all the words in it. Then you can pass it in dynamic SOQL as well like you pass in normal SOQL query. That would work.

Below is the sample code:

List<String> s = new List<String>{'Burlington%','%plc'};
String query = 'select id, name from Account where name like :s'; 
List<Account> a = Database.query(query);
Daniel Ballinger
  • 102,288
  • 39
  • 270
  • 594
Mr.Frodo
  • 5,804
  • 1
  • 16
  • 37