Re: [ib-support] UPPER on calculated field with WIN1252/PXW_SWEDFIN
----- Original Message -----
From: "Ivan Prenosil" <prenosil@...>
Sent: Monday, June 24, 2002 4:25 PM
Subject: Re: [ib-support] UPPER on calculated field with WIN1252/PXW_SWEDFIN
> > FULL_NAME COMPUTED BY (LTRIM(XNAME || ' ' || NAME)));
> > SELECT * FROM testtable_III
> > WHERE UPPER(FULL_NAME) CONTAINING 'ER FRÅN';
> 1) CONTAINING is case insensitive operator, so you do not have to combine
it with UPPER().
That is exactly why I combine it with UPPER (to make it not case sensitive).
> 2) both CONTAINING and UPPER needs to know collation of their arguments to
> correctly with national characters.
> By using LTRIM udf you lost that information for FULL_NAME column.
> (and updating system table probably does not work as expectet)
I see, this is the cause of my problem!
> Now you have several possibilities:
> -Specify collation in WHERE clause
> WHERE FULL_NAME COLLATE PXW_SWEDFIN CONTAINING 'ER FRÅN';
Think I've tried that - did not work (w/ regard to UPPER). I'll try it again
when I have the time.
> -Do not use LTRIM at all.
> Either declare computed column
> FULL_NAME COMPUTED BY (XNAME || ' ' || NAME));
That is not an option. Though I might store the computed in another field
with a trigger.
> or do not use computed field in where clause at all, e.g.
> WHERE XNAME || ' ' || NAME CONATINING 'ER FRÅN';
> -Rephrase your query, e.g.
> WHERE XNAME CONATINING 'ER' AND XNAME CONTAINING 'FRÅN' OR ... ;
Definately not an option for me since the wuery is generated by client