I have this DBISAM 3 SQL(simplified):

select * from groups
where MainGroup between '20 ' and '20 ÿÿÿÿÿÿ'

Now I have the problem , that it dosn't return what I expect. My groups
table has the language Swiss(german) and for some strange reasons this query
handles the 'ÿ' (ASCII 255) as a 'y'. So I wonder how I can do what I want
with my query? Both between values are from a user input, who must enter a
from/to pair. So a SQL like "20%" will not work as my code must use a
from/to value pair. The Between should return the same records as a "like".

If I convert my table to ansi, it works correct, but now the sorting isn't
anymore correct. What can I control the SQL which language it sould use for
searching? In this case my users want all record beginning with '20 ' but I
can't see how they can enter the 'to' value correctly to get what they want.

Regards
Rolf

Rolf


Pad the two strings after the user has entered them. For the first value pad it with the first character in your sorting sequence and for the second pad it with the last.

This works for ranges and I assume for SQL

Roy Lambert

Rolf,

<< Now I have the problem , that it dosn't return what I expect. My groups
table has the language Swiss(german) and for some strange reasons this query
handles the 'ÿ' (ASCII 255) as a 'y'. So I wonder how I can do what I want
with my query? Both between values are from a user input, who must enter a
from/to pair. So a SQL like "20%" will not work as my code must use a
from/to value pair. The Between should return the same records as a "like".

If I convert my table to ansi, it works correct, but now the sorting isn't
anymore correct. What can I control the SQL which language it sould use for
searching? In this case my users want all record beginning with '20 ' but I
can't see how they can enter the 'to' value correctly to get what they want.
>>

You can't do what you want without knowing the highest character of the
collation used in the table and using it to pad the second value.  For
German (Switzerland), the highest character is:

Chr(142) or Z

--
Tim Young
Elevate Software
www.elevatesoft.com

But I want all records with higer ascii values not only until 'z'. Else all
records with ascii values higher than 'z' will not come. So records with '20
®' will not come. I want to do a select of all records larger than '20 ' but
smaller than '20 ÿ'. '20 ®' must be returned here as it is between the two
values, but DBISAM doesn't return me this records

Regards
Rolf


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schrieb im
Newsbeitrag
news:F56C6159-E4CC-492E-AAD5-35C01D215FE0@news.elevatesoft.com...
> Rolf,
>
> << Now I have the problem , that it dosn't return what I expect. My groups
> table has the language Swiss(german) and for some strange reasons this
> query handles the 'ÿ' (ASCII 255) as a 'y'. So I wonder how I can do what
> I want with my query? Both between values are from a user input, who must
> enter a from/to pair. So a SQL like "20%" will not work as my code must
> use a from/to value pair. The Between should return the same records as a
> "like".
>
> If I convert my table to ansi, it works correct, but now the sorting isn't
> anymore correct. What can I control the SQL which language it sould use
> for searching? In this case my users want all record beginning with '20 '
> but I can't see how they can enter the 'to' value correctly to get what
> they want.
> >>
>
> You can't do what you want without knowing the highest character of the
> collation used in the table and using it to pad the second value.  For
> German (Switzerland), the highest character is:
>
> Chr(142) or Z
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Rolf,

<< But I want all records with higer ascii values not only until 'z'. Else
all records with ascii values higher than 'z' will not come. So records with
'20 ®' will not come. I want to do a select of all records larger than '20 '
but smaller than '20 ÿ'. '20 ®' must be returned here as it is between the
two values, but DBISAM doesn't return me this records >>

Actually, the accent was stripped from my posting for Chr(142).  It isn't
supposed to say plain 'Z', but rather Chr(142).  Or are you saying that this
is not working even when you use Chr(142) ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Ok this seems to work, but it's not clear to me why?

select * from groups
where MainGroup between '20' and '20 ŽŽŽŽŽŽŽŽŽŽŽ'

ASCII 142 is smaller then ÿ CHAR(255), but why does now the above sql return
record with CHR(255) values correctly as I want? What about other Languages
like german or italian? How can I find out which char to use for that
language?

Regards
Rolf


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schrieb im
Newsbeitrag
news:9D418694-0371-4CFA-AA89-ECE32DB744EC@news.elevatesoft.com...
> Rolf,
>
> << But I want all records with higer ascii values not only until 'z'. Else
> all records with ascii values higher than 'z' will not come. So records
> with '20 ®' will not come. I want to do a select of all records larger
> than '20 ' but smaller than '20 ÿ'. '20 ®' must be returned here as it is
> between the two values, but DBISAM doesn't return me this records >>
>
> Actually, the accent was stripped from my posting for Chr(142).  It isn't
> supposed to say plain 'Z', but rather Chr(142).  Or are you saying that
> this is not working even when you use Chr(142) ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Rolf,

<< Ok this seems to work, but it's not clear to me why? >>

It works because according to the German (Swiss) collation Chr(142) is the
last character in the sort order.

<< ASCII 142 is smaller then ÿ CHAR(255), >>

It isn't using the German collation.

<< but why does now the above sql return record with CHR(255) values
correctly as I want? What about other Languages like german or italian? How
can I find out which char to use for that language? >>

You can find out the highest character by simply populating a table with
every character in the ANSI character set using a specific collation with a
1 character string field and an index on that field.

--
Tim Young
Elevate Software
www.elevatesoft.com