Re: [ib-support] UPPER on calculated field with WIN1252/PXW_SWEDFIN
Author
Ar Bit
Post date
2002-06-25T13:41:46Z
----- Original Message -----
From: "Ivan Prenosil" <prenosil@...>
To: <ib-support@yahoogroups.com>
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
work
> 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
software.