SOQL on Rich text fields long text

Question

I'm working on a custom search page, which will search through the fields of the Project object. I use dynamic SOQL query similar to that:

SELECT Id, 
            Name, 
            Link_Test__c, 
            Technology_Sinonym__c, 
            Internal_Synonym__c, 
            Innovators__c 
    FROM Technology__c 
    WHERE(Technology_Limitations__c != 'ddd' AND Name != 'www') 
    ORDER BY Name DESC LIMIT 50

but the error that display :

Error: field 'Technology_Limitations__c' can not be filtered in query call

this filed id Rich Text so I know I cant use where operator in SOQL so I am wonder if I can use in SOSL or Do you have author solution

Answer 1

I just discovered a work around for this here. Create a Formula field that uses the LEFT formula and filter on that. You cannot filter on the ENTIRE long text but you can filter on part of it:

Legal_Street__c is a long text area. I created a formula field call StreetConc__c:

LEFT(Legal_Street__c, 40)

I then created a dummy record and put the text 'testingthis' in the Legal_Street__c field. From here I filtered on this formula field.

System.debug('answer = ' + [SELECT Name FROM Account WHERE StreetConc__c LIKE 'testing%']);

You should be able to apply this to both SOQL and SQL.

NOTE: You don't need to use 40 for the LEFT in the formula field. It was just an example.