��� in an index

[Ludwig Beil]

german letters like ���� (which are ae oe ue) in a4v6 and a5v4

In a4v6 I can set an index for a city database and have the cities starting with special letters like ��� near the aou.
In a4v5 those cities come at the end of the database. When trying to use the existing dbf; Iget strange letters for those german letters and have to look after each record.
(The windows settings are set for german and metric system).

[whanigsberg]

RE: ��� in an index

I have worked with this issue. The general approach is to use the accented characters in the data but to replace them with their unaccented equivalents in the index expression. In other words, in ordering names by last name my colleague Jacques whose last name is "Melan�on" will be after "Melis" but if I replace "�" with "c" in the index expression he is ordered before "Melis".

So the required index expression is:
function_to_remove_accents(lastname+firstname)
where the function is a custom function. Lastname and firstname are of course the fields being indexed.

The custom function (as usual) was produced with the aid of Ira Perlow. You can add other characters to the list for replacement. Test carefully. If you have machines with different setups (code pages) you may have problems. I did with a couple of deviant French machines but I cannot be sure it was this function which caused them.

Bill

function No_accents as C(input_string as C)

' Replaces accented characters with unaccented equivalents for sorting and indexing

oldvals="����������"
newvals="EECeeecaao"

tmpstr = ""

for i = 1 to len(input_string)
vChar = substr(input_string,i,1)
position=AT(vChar,oldvals)

if position>0
vChar=SUBSTR(newvals,position,1)
end if

tmpstr = tmpstr+vChar
next

no_accents = tmpstr

end function

[csda1]

RE: ��� in an index

Hi Bill,

In recent times, I've done some studies as to the time that certain xbasic code takes.

Some basic concepts for speed are

1. Commented lines don't take longer to execute
2. Code that is skipped (e.g. the false conditions of an IF or CASE Xbasic statement) add almost no overhead
3. 2 assignments, e.g.
a=substr(char,i,1)
tmp=tmp+a
take almost twice as long as
tmp=tmp+substr(char,i,1)
take almost twice as long as the single assignment. The exception would be if you had to use the "a" value more than once in other code

4. Internal A5 functions are always preferable to XBasic code no matter how complex they are. (STRTRAN() is in my example below)

5. In case A5 re-evaluates the ending value for each pass of the FOR loop, precalculate the value and then apply it in the FOR statement

6. Never calculate the same value twice (e.g. vChar in your example is a correct way)

For your code, the 1st speedup I would consider is as follows;


function No_accents as C(input_string as C)

'Replaces accented characters with unaccented equivalents for sorting and indexing

oldvals="����������"
newvals="EECeeecaao"

tmpstr = ""

lenstg=len(input_string)
for i = 1 to lenstg
vChar = substr(input_string,i,1)
position=AT(vChar,oldvals)

if position>0
tmpstr = tmpstr+SUBSTR(newvals,position,1)
else
tmpstr = tmpstr+vChar
end if

next

no_accents = tmpstr

end function

However, since this is a key routine that is needed in the indexing, it needs to be even faster if possible. While I wouldn't be overly concerned for some routines, this is a case where absolute speed is probably very important. The code below is about 3 times faster

function No_accents as C(input_string as C)
' Replaces accented characters with unaccented equivalents for sorting
' and indexing. Newvals and oldvals should be the same length

oldvals="����������"
newvals="EECeeecaao"

no_accents=input_string

lenstg=len(newvals)
for i=1 to lenstg
no_accents=strtran(no_accents,substr(oldvals,i,1),substr(newvals,i,1))
next

end function

The above routines time is bounded almost entirely by the length of newvals (or oldvals). The length of the input_string has almost no effect on execution time

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[whanigsberg]

RE: ��� in an index

Ira,

Well you've done it again. Before too much more time passes you will have transformed me into an educated person. Thanks so much for an absolutely fascinating exploration. The best part is that there are principles here which can be generalized and applied elsewhere.

All the best,
Bill

[Ludwig Beil]

RE: ��� in an index

Thanks for your help!!!!
I hope there is no remarkable speed difference when handling this way with 20000 records.
Lu

[Ludwig Beil]

RE: ��� in an index

well done.
One problem lasts. Each letter repalces one other letter exactly but how can I replace by two?
� is equivalent to ae so how can I replace � by ae � by oe and � by oe?

Lu

[csda1]

RE: ��� in an index

Ludwig,

Just modify the expression simply as

function No_accents as C(input_string as C)
' Replaces accented characters with unaccented equivalents for sorting and indexing

oldvals="� � � � � � � � � � � � � � "
newvals="E E C e e e c a a o Aeaeoeoe"

no_accents=input_string

lenstg=len(newvals)
for i=1 to lenstg step 2
no_accents=strtran(no_accents,trim(substr(oldvals,i,2)),trim(substr(newvals,i,2)))
next

end function

My question is would the string "aof" come before or after "a�c" Depending on what you want, you may have to translate the o to o!, A to A! etc to make sure the regular alphabet characters show up where you want in the sort.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[csda1]

RE: ��� in an index

Hi Bill,

I forgot one of the most useful spped enhancing techniques, referred to as "unrolling a loop"

While less generic, this runs about 5.5 times faster than your original example

function No_accents as C(input_string as C)
' Replaces accented characters with unaccented equivalents for sorting and indexing

no_accents=input_string

no_accents=strtran(no_accents,"�","E")
no_accents=strtran(no_accents,"�","E")
no_accents=strtran(no_accents,"�","C")
no_accents=strtran(no_accents,"�","e")
no_accents=strtran(no_accents,"�","e")
no_accents=strtran(no_accents,"�","e")
no_accents=strtran(no_accents,"�","c")
no_accents=strtran(no_accents,"�","a")
no_accents=strtran(no_accents,"�","a")
no_accents=strtran(no_accents,"�","o")

end function

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[csda1]

RE: ��� in an index

Hi Bill,

And just to obsess to the n'th degree, I combined unrolling the loop with less assignments and got this version that's about 8 times faster than your original.

function No_accents as C(input_string as C)
' Replaces accented characters with unaccented equivalents for sorting
' and indexing
no_accents=strtran(strtran(strtran(strtran(strtran(strtran(\
strtran(strtran(strtran(strtran(input_string,"�","E"),"�","E")\
,"�","C"),"�","e"),"�","e"),"�","e")\
,"�","c"),"�","a"),"�","a"),"�","o")

end function

The continuation line character backslash seems to have no effect on the final speed in case you are wondering.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Ludwig Beil]

RE: ��� in an index

super, super, super!!!!

[whanigsberg]

RE: ��� in an index

Hi again,

Ira, are you aware of any reliability issues connected to using user-defined functions in index expressions. I suppose there might be a speed penalty but is there anything else to worry about?

Thanks,
Bill

[csda1]

RE: ��� in an index

Hi Bill,

Yes. Using a user-defined function, or any, non-standard dBase III/IV function for that matter in the index expression makes the index expression (stored in the *.cdx file) incompatible with other programs that may use the index directly. This would typically be another dBase III/IV compatible program using and changing the index outside the confines of Alpha 5. Using an ODBC connection would not have this problem.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Ludwig Beil]

RE: ��� in an index

Hi, Ira and others!
I�m new with a5, so I need a lot of help. I tried to use the function to change my index.

1. the table
Let�s say I have a simpe index for a table named customer.
Order expression is K_name

2. the function
I�ve tried to enter the following code with the Code Editor

function de_Umlaute as C()

oldvals="� � � � � � � "
newvals="AeOeUeaeoeue�!"

de_Umlaute=input_string
lenstg=len(newvals)
for i=1 to lenstg step 2
de_Umlaute=strtan(de_Umlaute,trim(substr(oldvals,i,2)),trim(substr(newvals,i,2)))

next

end function

3. saving
I then saved this function and tried to use it in the Index Builder. I found the new function in the list, so I tried to enter
de_Umlaute(K_name) or de_Umlaute(trim(K_Name)) under order expression and save it I got an error saying "Too many parameters"

Where�s my fault?????

L. Beil
EMAIL: [email protected]

[whanigsberg]

RE: ��� in an index

Ludwig:

You made a small typo. Try
function de_Umlaute as C(input_string as c)

Bill

[Ludwig Beil]

RE: ��� in an index

I tried that. These are my conditions:
My customer table has a field named K_name.
I have created an index Name01 with an expression expression trim(K_name).

I tried to use the index expressions:
de_Umlaute(trim(K_Name)
de_umlaute(K_Name)
de_Umlaute(trim(K_Name)
which were created with the expression builder. Are those valid expressions or should they be named like

function de_Umlaute(trim(K_Name) ???

When I use the first ones I get an error saying

command:
de_Umlaute=strtan(de_Umlaute,trim(substr(oldvals,i,2)),trim(substr(newvals,i,2))
function is not recognized
This message only shows two ) at the end of the expression. In the expression builder I have 3)
Why???????



L. Beil
EMAIL: [email protected]