How to SCAN substrings in a dbf - Summer 87

[marge0512]

Hello, I need a command that works like the "Like" command in SQL. Our system was programmed in Clipper87 and we have to type in the exact company name using hyphens and everything else. If you don't type the name in the exact way, you would get Record Not Found.

For example,searching for "The Burling - Huntor Store" would have to be typed in that exact way in order to find that record. Is there a way that i could just type in "The Bur" and it would find it?? I would also like to have all records to sort from "The Bur" so the user may easily select the row.

I hope I'm explaining this in a way that is understandable.

This is what I have now:

Código: Selecionar todos

procedure REC_FND
private REC2FIND, SAV_REC
* Save current position in database if search fails
*--------------------------------------------------
SAV_REC = recno()

set color to &GET_CLR
read

* Continue if escape wasn't pressed
*----------------------------------
if LASTKEY()#ESC_KEY
		
			*--------------------------------
			REC2FIND = space(40)
			do DISP_MSG with 'Client (include punctuation): '
			@row(),col() get REC2FIND
			read

			* Continue if the escape key was not pressed
			*-------------------------------------------
			if lastkey() # ESC_KEY

				* Remove trailing spaces
				*-----------------------
				REC2FIND = upper(trim(REC2FIND))

				* Get client index
				*--------------------
				set order to 7

				* Search using the key
				*---------------------
				set exact off
				seek REC2FIND
				set exact on

				* Return to the first index
				*--------------------------
				set order to 1

				* If found, put the highlight bar on the record
				*----------------------------------------------
				if !eof()
					do SCRL_TOS
				* If not found, tell the user
				*----------------------------
				else
					do DISP_MSG with [Client, ]+trim(REC2FIND)+[, not found.  Press a key.]
					inkey(0)
					go SAV_REC
				endif
			endif

endif

return
* 

Thanks in advance!

Nota de Moderação:
por Pablo César: Title was modified to keep same matter in different situations.

[][]

[fladimir]

(Using sometimes Google Translator)

Welcome friend...

If I understand all a way to do this is create a temporary index with the key that you want, where you receive only the records that contain your key...
For you have a return how the "Like" command in SQL you use the operator '$'

For example:

Código: Selecionar todos

Procedure Main
//-- Declare your variables how PRIVATE because in FOR expressions if you use 'Macro' (&) you will need 
PRIVATE cYourInput := ''
PRIVATE bFilter := ''

cYourInput := "The Bur"
bFilter := "cYourInput $ cMyField"  //-- Here you put something how this.

Use Table Alias Test
Index on FieldName to IndexName FOR &bFilter

//-- Here you will only show what was filtered in work area indexed
While Test->(!EOF())
   ? 'Your expression' + Test->cMyField, Test->YourFields, Test->OtherField, Test->EtcField
   Test->(dbskip())
END

Test->(dbclosearea())

Return NIL

Best Regards...

[alxsts]

Hi!

Just to clarify:

in the above example, the "dollar operator" ($) was used. It works like the SQL LIKE command: starts with ('%like'), ends with ('like%') or contains ('%like%').

See details and example below:

$
Substring comparison--binary (Relational)
------------------------------------------------------------------------------
Syntax

<cString1> $ <cString2>

Type

Character, memo

Operands

<cString1> is a character or memo value that is searched for in
<cString2>.

<cString2> is a character or memo value within which <cString1> is
sought.

Description

The $ operator is a binary relational operator that performs a case-
sensitive substring search and returns true (.T.) if <cString1> is found
within <cString2>.

Examples

. This example illustrates the case-sensitivity of the substring
operator ($):

? "A" $ "ABC" // Result: .T.
? "a" $ "ABC" // Result: .F.

[Maligno]

The tips of our friends are valid, but you need to pay attention to an important point: you always have to enter the names correctly, otherwise you will not find anything. It's an easy problem to predict.

So I want to leave a tip: in the future, consider using something like SOUNDEX algorithm (not in Summer'87, of course), where you will have possibility to enter any name, even with small errors. The names, especially names of people, often are difficult to write.

Note: the SOUNDEX algorithm, as it was originally conceived (early 20th century), is not ideal. You need to use something more modern to get a really good result.

[rochinha]

Hi little friend,

With this post you can create selects like SQL in pure Clipper

See here

[Euclides]

Amiguinho Rochinha...
O .CH no ´post´ citado, é da classe TSelector que é um Controle do Fivewin (por sinal, criado pelo nosso amiguinho Ramón Avedaño).
V. teria o .CH pertinente?
[]´s e Feliz 3*11*61
Euclides

[rochinha]

Amiguinho,

Peguei o conteúdo do arquivo errado. Tem o mesmo nome mas o conteúdo era diferente. Hehehe! demorei 6 anos para perceber o erro. Graças a voce, e conhecedor das ferramentas que usa, fui alertado.

Já está retificado.

[alxsts]

Hi!

Fladimir's code will not work because the FOR clause is not suported in Clipper Summer'87 INDEX command. Neither the use of inline assignment operators (:=).

Summer'87 Syntax: INDEX ON <key exp> TO <file>/(<expC>)

[rochinha]

Friend,

Change ":=" for "=".

Try:

Código: Selecionar todos

Index on FieldName to IndexName FOR &(bFilter)

Or:

Código: Selecionar todos

Index on FieldName to IndexName FOR "&bFilter"

[alxsts]

Olá!

Segundo a sintaxe no manual do Clipper Summer'87, a cláusula FOR não existe no comando INDEX...

[marge0512]

Thanks for all of your responses. I'm sorry I'm just getting back this now. I was out for a few days.

I am slightly confused though.......I only work with Clipper when the user needs a change which is about once a year so I consider myself still a newbie.........is there anyway i can incorporate the dollar operator into my existing code?

The user needs the search to work as if it was the %like% command. So, for example, if he wanted to search for "The Burlington" and doesn't remember if he entered the information with the word "The", he could enter "Bur" and still find "The Burlington"?? Would I still need to use the function that was given?

[alxsts]

Hi!

I've made some changes to your original code. Please, check to see if it meets what your user needs.

Código: Selecionar todos

procedure REC_FND
private REC2FIND, SAV_REC
* Save current position in database if search fails
*--------------------------------------------------
SAV_REC = recno()

set color to &GET_CLR
read

* Continue if escape wasn't pressed
*----------------------------------
if LASTKEY()#ESC_KEY
      
   *--------------------------------
   REC2FIND = space(40)
   do DISP_MSG with 'Client (include punctuation): '
   @row(),col() get REC2FIND
   read

   * Continue if the escape key was not pressed
   *-------------------------------------------
   if lastkey() # ESC_KEY

      * Remove trailing spaces
      *-----------------------
      REC2FIND = upper(trim(REC2FIND))

      * cannot use an index to search partially. Go top and perform full table scan...
      *-----------------------------------------------------------------      
      GO TOP
      
      DO WHILE LASTKEY() # ESC_KEY .And. ( .Not. Eof() )
      
         IF REC2FIND $ UPPER( <customer-name-Field> ) * =============> suplly the correct field name here
            * If found, put the highlight bar on the record
            do SCRL_TOS
            
            * await for user input
            do DISP_MSG with [Client, ]+trim(REC2FIND)+[, found.  Press <ESC> to EXIT, any other key to RESUME searching...]
            
            INKEY(0) * =============> if DISP_MSG generates a wait state, remove this line
         ENDIF
         
         SKIP            

      ENDDO   

      IF EOF() .And. LASTKEY() # ESC_KEY
         do DISP_MSG with [Client, ]+trim(REC2FIND)+[, not found.  Press a key.]
         inkey(0)
         go SAV_REC
      ENDIF            

      *----------------------------------------------

   endif
endif

return
* 

[rochinha]

Friend,

Uses for dollar operator:

This cause .T. response.

Código: Selecionar todos

...
set filter to "Bur" $ "The Burlington"
...

Maybe here, causes .F. response.

Código: Selecionar todos

...
set filter to "BUR" $ "The Burlington"
...

Equalize the search

Código: Selecionar todos

...
set filter to upper( "Bur" ) $ upper( "The Burlington" )
...

In all these cases the search had been too slow

For all uses:

Código: Selecionar todos

...
set filter to upper( M->MyVar ) $ upper( Field->MyFieldName )
...

[marge0512]

Thanks for the code! I tried this and played around with it a little but it seems to select what i want it to search for plus more. I'm not sure why. For example, I chose the word "Inc" (which will select many) and it will bring back records that have the word "Inc" but will also select records that do not have the word "Inc" and these records will show up in between the records that do have "Inc". I will keep trying.

[alxsts]

Hi!

Seems so strange... if possible, attach a small DBF with sample data.

I placed the search ($ command) inside a loop. Is it what you need?

Check details. Sometimes the problem is very well hidden...