John Jaabæk wrote:

> Since I live in Norway, Scandinavia and having three letters you don't have
> times 2, I'm really having fun doing searching the database. Divide by zero
> when searching on Scandinavian letters. So I need to replace them with
> wildcards. I know how. Simple procedure or UDF or done inside PHP. Wherever
> But which wildcards are to be used??
>
> '%' - works as first charachter on the line. Not as number 2 or later.
> '*' - does not work at all.
> '?' - does not work at all.
>
> LIKE is handling '%' as first character.
> CONTAINING is handling none. It is a wildcard search, but not good enough.
>

Will this help? (Norwegian letters are probably not shown correctly)

The following returns 1:
-- lowercase Norw. letters in string
select count(*)
from rdb$database
where 'æøå' collate no_no containing 'Å'
as does:
-- uppercase Norw. letters in string
select count(*)
from rdb$database
where 'ÆØÅ' collate no_no containing 'å'
This returns 0:
select count(*)
from rdb$database
where 'æøå' containing 'Å'

The character set of the database is ISO8859_1, and I also specify
ISO8859_1 when I connect. I'm using Fb/1.5.2.


However (here, JOURNAL.OMTALE is a blob field)
select count(*)
from JOURNAL
where OMTALE containing 'å'
gives a different answer than
select count(*)
from JOURNAL
where OMTALE containing 'Å'
The counts are 16875 and 1274 resp. - adding "collate no_no" to 'å' or 'Å'
makes no difference.
Don't ask me why.



So, with a char/varchar field, try to use
select count(*)
from JOURNAL
where upper(SRT_NOR collate no_no) containing 'Å'
The upper/collate will not work with a blob (gives: Data type unknown
Invalid use of CHARACTER SET or COLLATE).


--
Aage J.