Subject Re: touppercase
From Mervyn Bick <invalid@invalid.invalid>
Date Fri, 7 Oct 2022 11:42:36 +0200
Newsgroups dbase.getting-started
Attachment(s) upper.png, toUpperCase.png, fish_like.wfm

On 2022/10/07 10:26, Helen Prior wrote:
>
> Dear Mervyn
 >
> Thank you for the upper/lower - That partially works but fails if the first character is in upper and the rest of the search word is in lower.
 > Going back to touppercase
 > Will this command permanently lower case to upper case? - If so how might that be achieved?
 > That might hopefully be the answer - It is so frustrating that dbase/sql cannot search ignoring upper/lower case
 > Regards
 > Helen

Upper() and lower() are functions in both dBASE and localSQL.  They
accept a string as an argument and return a new copy of the string
converted to all upper case or all lower case.  These functions do not
alter the string passed as an argument unless the programmer deliberate
writes the returned value back into the source.

Try the following in the Command Panel

a = 'This is a Test'
?a
?lower(a)
?upper(a)
?a
a = upper(a) //Write the upper case text back to the variable
?a

An attached screenshot shows the results.

toUpperCase() is also a function which returns a value without changing
the argument passed to it but as it is tied to a string object it is
called a method.  In this case it doesn't take an argument between the
brackets but acts on the string contained in the object's value
property.  As with the stand-alone functions it it does not change the
source value unless the programmer does this deliberately.

Try the following in the Command Panel

oStr = new string()
oStr.value = 'This is a Test'
?oStr.value
?oStr.value.toUpperCase()
?oStr.value
oStr.value = oStr.value.toUpperCase() //Force oStr.value to upper case
?oStr.value

An attached screenshot shows the results.


Some dBASE commands do have the facility to be set either case-sensitive
or case-insensitive.  localSQL is case-sensitive but the use of upper()
or lower() allows the programmer to get round this.

To make your search case-insensitive remember that you need to make both
the contents of the field and the search value the same case.

Form.DAILYLOG2.sql = [select * from DAILYLOG where upper(Keyevent) like
'%] + upper(Vkeyevent) + [%' or upper(History) like '%]
+upper(Vkeyevent) + [%']

Instead of building a new SELECT statement each time you need to search
for a different keyevent value you could use a parameter driven query.
To display a different set of records simply pass a new parameter to the
query and requery().  A little example form is attached.  This only
searches on a single field but a second field can be added in the same
way as with the "built" SELECT statement.

Mervyn.













** END HEADER -- do not remove this line
//
// Generated on 2022-10-07
//
parameter bModal
local f
f = new fish_likeForm()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class fish_likeForm of FORM
   with (this)
      height = 21.0909
      left = 18.7143
      top = 0.1818
      width = 112.2857
      text = ""
   endwith

   this.DBASESAMPLES1 = new DATABASE(this)
   with (this.DBASESAMPLES1)
      left = 3.0
      width = 11.0
      height = 1.0
      databaseName = "DBASESAMPLES"
      active = true
   endwith

   this.FISH1 = new QUERY(this)
   with (this.FISH1)
      left = 21.0
      width = 3.0
      height = 1.0
      database = form.dbasesamples1
      sql = "select * from FISH.DBF where upper(name) like upper(:name)"
      params["name"] = "%"
      active = true
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.fish1.rowset
      height = 13.2727
      left = 2.8571
      top = 4.1818
      width = 104.1429
   endwith

   this.PUSHBUTTON1 = new PUSHBUTTON(this)
   with (this.PUSHBUTTON1)
      onClick = class::PUSHBUTTON1_ONCLICK
      height = 1.8182
      left = 8.8571
      top = 18.5455
      width = 15.2857
      text = "Records containing"
   endwith

   this.PUSHBUTTON2 = new PUSHBUTTON(this)
   with (this.PUSHBUTTON2)
      onClick = class::PUSHBUTTON2_ONCLICK
      height = 1.8182
      left = 54.1429
      top = 18.5455
      width = 15.2857
      text = "All records"
   endwith

   this.ENTRYFIELD1 = new ENTRYFIELD(this)
   with (this.ENTRYFIELD1)
      height = 1.0
      left = 26.0
      top = 19.0
      width = 18.0
      value = "blUE"
   endwith

   this.rowset = this.fish1.rowset

   function PUSHBUTTON1_onClick()
      form.fish1.params['name'] = '%'+form.entryfield1.value+'%'
      form.fish1.requery()
      return

   function PUSHBUTTON2_onClick()
      form.fish1.params['name'] = '%'
      form.fish1.requery()      
      return

endclass