Using INSTRING to mimic SQL's LIKE in Record Filter

#1

Hi, i have been working on a project in CLARION 9, and I need to use a search bar to filter the contents of a browse, but I haven’t been able to do it with INSTRING(), some times it works but sometimes it doesn’t. I was wondering if there’s another way (without creating other files) to do a search with the same accuracy as a LIKE in SQL.
The way i’m using in the RecordFilter:
INSTRING(LOCSearch,TAB:Field1,1,1) > 0

#2

Do you perhaps need to CLIP LOCSearch?
What database driver are you using?

#3

Tried clipping, no result, I’m using MSSQL

#4

If you’re using MSSQL, why not use LIKE?

If you’re trying to just filter the result set, try:

INSTRING(UPPER(CLIP(LOC:Search)),UPPER(TAB:Field1),1,1)

#5

Can I alter the query that the Browse uses when the window opens? I haven’t been able to do that…

#6

As far as SQL goes, I’m not sure what ABC has. But for typical filters, you can use YourBrowse.SetFilter(). Be sure to read the docs on it, especially the 2nd parameter.

But you also have PROP:SQLFilter which you can use on YourBrowse.View

#7

Have you used profiler to trace what is actually being sent to the back end server when the filter fails?

You can only control some aspects of of the statements generated. What specifically are you asking about?

#8

Use LIKE as it is meant to be used.

In the Apply Filter method, before parent (using eSQuote as EQUATE(’<39>’)

MyTable{PROP:Alias} = ‘t’
SELF.SetFilter(‘SQL(t.myfield LIKE’ & eSQuote & ‘%’ & MyVal & ‘%’ & eSQuote & ‘)’),MyFilter#)

#9

I use this bit of template code to force the Alias of every SQL table to be the prefix of the table at the global level. That way you do not need to determine or set the table alias in your procedure code.

#AT(%FileManagerCodeSection,,'Init','()'),PRIORITY(5500),WHERE(%FileDriver = 'MSSQL' AND ~%GlobalExternal)
Self.File{Prop:Alias} = '%FilePrefix' 
#ENDAT
#10

Sorry, i’m don’t understand this code, what is the Apply Filter Method? And SELF?

#11

Have you tried the instring() without the >0 ? At some point the Clarion drivers recognize that and translate it to an SQL LIKE all by themselves. But I can’t remember when that started.

Are you using Clarion or ABC templates?

#12

Apologies if confusing. When you stated browse, my head immediately assumed ABC browse. ApplyFilter is a method of that class.