SOQL search with LIKE

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
2
down vote

favorite

IÃ¢Â€Â™m very confused with the soql Like operator.

If I have a record with a text field that may contain: "Orders to be shipped, pack blue labels/zip/ authorization slip needed.Ã¢Â€Â
And a user enters Ã¢Â€Âœblue authorizationÃ¢Â€Â into our search,It will not bring in that order at all.
Here is the query:

string search = 'SELECT id, Name, order_information__c from order WHERE (Name like '%' + searchstring + '%' OR order_information__c like '%' + searchstring + '%') Limit 50';
 Orders = Database.query(search);

Basically in our vf page we have a search that a user enters in whatever and it brings in orders that match in either name or order_information__c

If the user types in "blue label", it will bring in records but not "blue authorization".
How can we search the whole string if the search term may contain any of the words in the string?

SalesforceÃ¢Â€Â™s standard search is able to do this, that if a user just types in Ã¢Â€Âœblue authorizationÃ¢Â€Â it brings in any record with that in their order_information

update to reflect answer:

List chunks = searchString.split(' ');

List<String> fields = new List<String> 'Name', 'Order_Information__c' ;

String query = 'SELECT id, Name, Order_information__c from order WHERE ('; 
 for (String field:fields) 
 query += field + ' LIKE '%';

 for (String chunk:chunks) 
 query += ' ' + chunk + '%';
 
 query += '' OR'; 
 

query = query.removeEnd('OR'); 

 query += ') LIMIT 50';

System.debug(query);
 }

}

edited 1 hour ago

asked 4 hours ago

j.koh

153

I recommend changing the reflected answer to be cognisant of SOQL injection vectors, to be a bit smarter about handling the whitespace (not just spaces, but tabs etc.) and to avoid messing about with remembering to remove stuff you added to the string when you can avoid adding it in the first place rather easily. See my alternative answer.
â€“Â Phil W
1 hour ago

add a commentÂ |Â

up vote
2
down vote

favorite

IÃ¢Â€Â™m very confused with the soql Like operator.

string search = 'SELECT id, Name, order_information__c from order WHERE (Name like '%' + searchstring + '%' OR order_information__c like '%' + searchstring + '%') Limit 50';
 Orders = Database.query(search);

Basically in our vf page we have a search that a user enters in whatever and it brings in orders that match in either name or order_information__c

If the user types in "blue label", it will bring in records but not "blue authorization".
How can we search the whole string if the search term may contain any of the words in the string?

SalesforceÃ¢Â€Â™s standard search is able to do this, that if a user just types in Ã¢Â€Âœblue authorizationÃ¢Â€Â it brings in any record with that in their order_information

update to reflect answer:

List chunks = searchString.split(' ');

List<String> fields = new List<String> 'Name', 'Order_Information__c' ;

String query = 'SELECT id, Name, Order_information__c from order WHERE ('; 
 for (String field:fields) 
 query += field + ' LIKE '%';

 for (String chunk:chunks) 
 query += ' ' + chunk + '%';
 
 query += '' OR'; 
 

query = query.removeEnd('OR'); 

 query += ') LIMIT 50';

System.debug(query);
 }

}

edited 1 hour ago

asked 4 hours ago

j.koh

153

I recommend changing the reflected answer to be cognisant of SOQL injection vectors, to be a bit smarter about handling the whitespace (not just spaces, but tabs etc.) and to avoid messing about with remembering to remove stuff you added to the string when you can avoid adding it in the first place rather easily. See my alternative answer.
â€“Â Phil W
1 hour ago

add a commentÂ |Â

up vote
2
down vote

favorite

IÃ¢Â€Â™m very confused with the soql Like operator.

string search = 'SELECT id, Name, order_information__c from order WHERE (Name like '%' + searchstring + '%' OR order_information__c like '%' + searchstring + '%') Limit 50';
 Orders = Database.query(search);

Basically in our vf page we have a search that a user enters in whatever and it brings in orders that match in either name or order_information__c

If the user types in "blue label", it will bring in records but not "blue authorization".
How can we search the whole string if the search term may contain any of the words in the string?

SalesforceÃ¢Â€Â™s standard search is able to do this, that if a user just types in Ã¢Â€Âœblue authorizationÃ¢Â€Â it brings in any record with that in their order_information

update to reflect answer:

List chunks = searchString.split(' ');

List<String> fields = new List<String> 'Name', 'Order_Information__c' ;

String query = 'SELECT id, Name, Order_information__c from order WHERE ('; 
 for (String field:fields) 
 query += field + ' LIKE '%';

 for (String chunk:chunks) 
 query += ' ' + chunk + '%';
 
 query += '' OR'; 
 

query = query.removeEnd('OR'); 

 query += ') LIMIT 50';

System.debug(query);
 }

}

edited 1 hour ago

asked 4 hours ago

j.koh

153

IÃ¢Â€Â™m very confused with the soql Like operator.

string search = 'SELECT id, Name, order_information__c from order WHERE (Name like '%' + searchstring + '%' OR order_information__c like '%' + searchstring + '%') Limit 50';
 Orders = Database.query(search);

Basically in our vf page we have a search that a user enters in whatever and it brings in orders that match in either name or order_information__c

If the user types in "blue label", it will bring in records but not "blue authorization".
How can we search the whole string if the search term may contain any of the words in the string?

SalesforceÃ¢Â€Â™s standard search is able to do this, that if a user just types in Ã¢Â€Âœblue authorizationÃ¢Â€Â it brings in any record with that in their order_information

update to reflect answer:

List chunks = searchString.split(' ');

List<String> fields = new List<String> 'Name', 'Order_Information__c' ;

String query = 'SELECT id, Name, Order_information__c from order WHERE ('; 
 for (String field:fields) 
 query += field + ' LIKE '%';

 for (String chunk:chunks) 
 query += ' ' + chunk + '%';
 
 query += '' OR'; 
 

query = query.removeEnd('OR'); 

 query += ') LIMIT 50';

System.debug(query);
 }

}

visualforce soql dynamic-soql

edited 1 hour ago

asked 4 hours ago

j.koh

153

edited 1 hour ago

asked 4 hours ago

j.koh

153

edited 1 hour ago

asked 4 hours ago

j.koh

153

asked 4 hours ago

j.koh

153

asked 4 hours ago

j.koh

153

I recommend changing the reflected answer to be cognisant of SOQL injection vectors, to be a bit smarter about handling the whitespace (not just spaces, but tabs etc.) and to avoid messing about with remembering to remove stuff you added to the string when you can avoid adding it in the first place rather easily. See my alternative answer.
â€“Â Phil W
1 hour ago

add a commentÂ |Â

I recommend changing the reflected answer to be cognisant of SOQL injection vectors, to be a bit smarter about handling the whitespace (not just spaces, but tabs etc.) and to avoid messing about with remembering to remove stuff you added to the string when you can avoid adding it in the first place rather easily. See my alternative answer.
â€“Â Phil W
1 hour ago

I recommend changing the reflected answer to be cognisant of SOQL injection vectors, to be a bit smarter about handling the whitespace (not just spaces, but tabs etc.) and to avoid messing about with remembering to remove stuff you added to the string when you can avoid adding it in the first place rather easily. See my alternative answer.
â€“Â Phil W
1 hour ago

add a commentÂ |Â

2 Answers
2

active

oldest

votes

up vote
2
down vote

accepted

Split the string provided by spaces, then put wildcards between each part of the search query.

If a user provides this text:

Blue Auth

Split it into this:

('Blue, 'Auth')

Then include it in your query like this:

String searchString = 'Blue Label'; // user provided 

List<String> chunks = searchString.split(' ');

List<String> fields = new List<String> 'Name', 'Order_Information__c' ;

String queryString = 'SELECT Id FROM Order__c WHERE ('; 

for (String field:fields) 
 queryString += ' ' + field + ' LIKE '%';

 for (String chunk:chunks) 
 queryString += chunk + '%';
 

 queryString += '' OR'; 


queryString = queryString.removeEnd('OR'); 

queryString += ') LIMIT 50';

System.debug(queryString);

Your end query should look like this:

SELECT Id FROM Order__c WHERE ( Name LIKE '%Blue%Label%' OR Order_Information__c LIKE '%Blue%Label%' ) LIMIT 50

edited 3 hours ago

answered 4 hours ago

battery.cord

6,43851742

I have tried your answer, but now when a user inputs anything in the searchstring it does not populate anything in the vf page
â€“Â j.koh
1 hour ago

nvm got it! thanks!
â€“Â j.koh
1 hour ago

j.koh, this answer contains issues such as exposure to SOQL injections. See my subsequent answer.
â€“Â Phil W
1 hour ago

add a commentÂ |Â

up vote
1
down vote

The answer from battery.cord needs some improvements, but gets you in the right direction.

I would suggest something more like:

String search = 'Blue Label';
List<String> fields = new List<String> 'Name', 'Order_Information__c' ;
List<String> fieldClause = new List<String>();
String cleanedSearch = '%' + search.trim().replaceAll('\s+', '%') + '%';

String query = 'SELECT Id FROM Order__c WHERE ';

for (String field : fields) 
 fieldClause.add(field + ' LIKE :cleanedSearch');


query += String.join(fieldClause, ' OR ');

System.debug(query);

The important points here:

You replace all whitespace with % characters, both around and within the search value

You leverage a binding variable for the search value, which avoids SOQL injection issues

You need to actually execute the query in the same method, since this is where the binding variable, 'cleanedSearch', is declared - so replace "System.debug(query)" with your "Database.query(query)" statement.

answered 1 hour ago

Phil W

1156

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',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
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%2f234394%2fsoql-search-with-like%23new-answer', 'question_page');

);

Post as a guest

Name

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
2
down vote

accepted

Split the string provided by spaces, then put wildcards between each part of the search query.

If a user provides this text:

Blue Auth

Split it into this:

('Blue, 'Auth')

Then include it in your query like this:

String searchString = 'Blue Label'; // user provided 

List<String> chunks = searchString.split(' ');

List<String> fields = new List<String> 'Name', 'Order_Information__c' ;

String queryString = 'SELECT Id FROM Order__c WHERE ('; 

for (String field:fields) 
 queryString += ' ' + field + ' LIKE '%';

 for (String chunk:chunks) 
 queryString += chunk + '%';
 

 queryString += '' OR'; 


queryString = queryString.removeEnd('OR'); 

queryString += ') LIMIT 50';

System.debug(queryString);

Your end query should look like this:

SELECT Id FROM Order__c WHERE ( Name LIKE '%Blue%Label%' OR Order_Information__c LIKE '%Blue%Label%' ) LIMIT 50

edited 3 hours ago

answered 4 hours ago

battery.cord

6,43851742

I have tried your answer, but now when a user inputs anything in the searchstring it does not populate anything in the vf page
â€“Â j.koh
1 hour ago

nvm got it! thanks!
â€“Â j.koh
1 hour ago

j.koh, this answer contains issues such as exposure to SOQL injections. See my subsequent answer.
â€“Â Phil W
1 hour ago

add a commentÂ |Â

up vote
2
down vote

accepted

Split the string provided by spaces, then put wildcards between each part of the search query.

If a user provides this text:

Blue Auth

Split it into this:

('Blue, 'Auth')

Then include it in your query like this:

String searchString = 'Blue Label'; // user provided 

List<String> chunks = searchString.split(' ');

List<String> fields = new List<String> 'Name', 'Order_Information__c' ;

String queryString = 'SELECT Id FROM Order__c WHERE ('; 

for (String field:fields) 
 queryString += ' ' + field + ' LIKE '%';

 for (String chunk:chunks) 
 queryString += chunk + '%';
 

 queryString += '' OR'; 


queryString = queryString.removeEnd('OR'); 

queryString += ') LIMIT 50';

System.debug(queryString);

Your end query should look like this:

SELECT Id FROM Order__c WHERE ( Name LIKE '%Blue%Label%' OR Order_Information__c LIKE '%Blue%Label%' ) LIMIT 50

edited 3 hours ago

answered 4 hours ago

battery.cord

6,43851742

I have tried your answer, but now when a user inputs anything in the searchstring it does not populate anything in the vf page
â€“Â j.koh
1 hour ago

nvm got it! thanks!
â€“Â j.koh
1 hour ago

j.koh, this answer contains issues such as exposure to SOQL injections. See my subsequent answer.
â€“Â Phil W
1 hour ago

add a commentÂ |Â

up vote
2
down vote

accepted

Split the string provided by spaces, then put wildcards between each part of the search query.

If a user provides this text:

Blue Auth

Split it into this:

('Blue, 'Auth')

Then include it in your query like this:

String searchString = 'Blue Label'; // user provided 

List<String> chunks = searchString.split(' ');

List<String> fields = new List<String> 'Name', 'Order_Information__c' ;

String queryString = 'SELECT Id FROM Order__c WHERE ('; 

for (String field:fields) 
 queryString += ' ' + field + ' LIKE '%';

 for (String chunk:chunks) 
 queryString += chunk + '%';
 

 queryString += '' OR'; 


queryString = queryString.removeEnd('OR'); 

queryString += ') LIMIT 50';

System.debug(queryString);

Your end query should look like this:

SELECT Id FROM Order__c WHERE ( Name LIKE '%Blue%Label%' OR Order_Information__c LIKE '%Blue%Label%' ) LIMIT 50

edited 3 hours ago

answered 4 hours ago

battery.cord

6,43851742

Split the string provided by spaces, then put wildcards between each part of the search query.

If a user provides this text:

Blue Auth

Split it into this:

('Blue, 'Auth')

Then include it in your query like this:

String searchString = 'Blue Label'; // user provided 

List<String> chunks = searchString.split(' ');

List<String> fields = new List<String> 'Name', 'Order_Information__c' ;

String queryString = 'SELECT Id FROM Order__c WHERE ('; 

for (String field:fields) 
 queryString += ' ' + field + ' LIKE '%';

 for (String chunk:chunks) 
 queryString += chunk + '%';
 

 queryString += '' OR'; 


queryString = queryString.removeEnd('OR'); 

queryString += ') LIMIT 50';

System.debug(queryString);

Your end query should look like this:

SELECT Id FROM Order__c WHERE ( Name LIKE '%Blue%Label%' OR Order_Information__c LIKE '%Blue%Label%' ) LIMIT 50

edited 3 hours ago

answered 4 hours ago

battery.cord

6,43851742

edited 3 hours ago

answered 4 hours ago

battery.cord

6,43851742

answered 4 hours ago

battery.cord

6,43851742

answered 4 hours ago

battery.cord

6,43851742

I have tried your answer, but now when a user inputs anything in the searchstring it does not populate anything in the vf page
â€“Â j.koh
1 hour ago

nvm got it! thanks!
â€“Â j.koh
1 hour ago

j.koh, this answer contains issues such as exposure to SOQL injections. See my subsequent answer.
â€“Â Phil W
1 hour ago

add a commentÂ |Â

I have tried your answer, but now when a user inputs anything in the searchstring it does not populate anything in the vf page
â€“Â j.koh
1 hour ago

nvm got it! thanks!
â€“Â j.koh
1 hour ago

j.koh, this answer contains issues such as exposure to SOQL injections. See my subsequent answer.
â€“Â Phil W
1 hour ago

I have tried your answer, but now when a user inputs anything in the searchstring it does not populate anything in the vf page
â€“Â j.koh
1 hour ago

nvm got it! thanks!
â€“Â j.koh
1 hour ago

j.koh, this answer contains issues such as exposure to SOQL injections. See my subsequent answer.
â€“Â Phil W
1 hour ago

add a commentÂ |Â

up vote
1
down vote

The answer from battery.cord needs some improvements, but gets you in the right direction.

I would suggest something more like:

String search = 'Blue Label';
List<String> fields = new List<String> 'Name', 'Order_Information__c' ;
List<String> fieldClause = new List<String>();
String cleanedSearch = '%' + search.trim().replaceAll('\s+', '%') + '%';

String query = 'SELECT Id FROM Order__c WHERE ';

for (String field : fields) 
 fieldClause.add(field + ' LIKE :cleanedSearch');


query += String.join(fieldClause, ' OR ');

System.debug(query);

The important points here:

You replace all whitespace with % characters, both around and within the search value

You leverage a binding variable for the search value, which avoids SOQL injection issues

You need to actually execute the query in the same method, since this is where the binding variable, 'cleanedSearch', is declared - so replace "System.debug(query)" with your "Database.query(query)" statement.

answered 1 hour ago

Phil W

1156

add a commentÂ |Â

up vote
1
down vote

The answer from battery.cord needs some improvements, but gets you in the right direction.

I would suggest something more like:

String search = 'Blue Label';
List<String> fields = new List<String> 'Name', 'Order_Information__c' ;
List<String> fieldClause = new List<String>();
String cleanedSearch = '%' + search.trim().replaceAll('\s+', '%') + '%';

String query = 'SELECT Id FROM Order__c WHERE ';

for (String field : fields) 
 fieldClause.add(field + ' LIKE :cleanedSearch');


query += String.join(fieldClause, ' OR ');

System.debug(query);

The important points here:

You replace all whitespace with % characters, both around and within the search value

You leverage a binding variable for the search value, which avoids SOQL injection issues

You need to actually execute the query in the same method, since this is where the binding variable, 'cleanedSearch', is declared - so replace "System.debug(query)" with your "Database.query(query)" statement.

answered 1 hour ago

Phil W

1156

add a commentÂ |Â

up vote
1
down vote

The answer from battery.cord needs some improvements, but gets you in the right direction.

I would suggest something more like:

String search = 'Blue Label';
List<String> fields = new List<String> 'Name', 'Order_Information__c' ;
List<String> fieldClause = new List<String>();
String cleanedSearch = '%' + search.trim().replaceAll('\s+', '%') + '%';

String query = 'SELECT Id FROM Order__c WHERE ';

for (String field : fields) 
 fieldClause.add(field + ' LIKE :cleanedSearch');


query += String.join(fieldClause, ' OR ');

System.debug(query);

The important points here:

You replace all whitespace with % characters, both around and within the search value

You leverage a binding variable for the search value, which avoids SOQL injection issues

You need to actually execute the query in the same method, since this is where the binding variable, 'cleanedSearch', is declared - so replace "System.debug(query)" with your "Database.query(query)" statement.

answered 1 hour ago

Phil W

1156

The answer from battery.cord needs some improvements, but gets you in the right direction.

I would suggest something more like:

String search = 'Blue Label';
List<String> fields = new List<String> 'Name', 'Order_Information__c' ;
List<String> fieldClause = new List<String>();
String cleanedSearch = '%' + search.trim().replaceAll('\s+', '%') + '%';

String query = 'SELECT Id FROM Order__c WHERE ';

for (String field : fields) 
 fieldClause.add(field + ' LIKE :cleanedSearch');


query += String.join(fieldClause, ' OR ');

System.debug(query);

The important points here:

You replace all whitespace with % characters, both around and within the search value

You leverage a binding variable for the search value, which avoids SOQL injection issues

You need to actually execute the query in the same method, since this is where the binding variable, 'cleanedSearch', is declared - so replace "System.debug(query)" with your "Database.query(query)" statement.

answered 1 hour ago

Phil W

1156

answered 1 hour ago

Phil W

1156

answered 1 hour ago

Phil W

1156

answered 1 hour ago

Phil W

1156

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky