>I've not been able to find any topic that comes close to addressing my
>question, and it seems so basic, I'm guessing that I'm missing something
>fundamental.
>
>I have queries that specify for ranges in varchar fields and return those and
>populate my VirtualStrings in a standard alphabetical grouping.
>
>The database I work with has some strings / varchar fields _begin_ with a
>_single_ or a _double_ quote, for emphasis or for identifying a literary work,
>etc.,
>for output in components where there is not an italic feature.
>
>Using the standard "<=" or ">=" works fine for varchar fields that begin with
>numbers and letters, but skips over those which begin with ''' <single-quote>
>or '"' <double-quote>.
>
>Is there some schema which I could have followed to find my way to the a nswer
>to this conundrum of mine?
>
>Ideally, I would prefer to have the varchar fields which begin with a
>single-quote or a double-quote collated in the proper order according to the
>first letter,
>regardless of upper or lowercase, or presence or lack of single- or
>double-quote.
>
No, Barry, this is not fundamental to a database (I would admit that it could
be considered fundamental to a programming language). I would recommend you to
create an auto-generated proxy column and use this for your sorting (whether or
not you include it in your result set). E.g. something like:
CREATE TABLE TEST_SORTING
(
PK INTEGER NOT NULL,
MYSTRING VARCHAR( 50) CHARACTER SET ISO8859_1,
STRIPPED VARCHAR( 50) CHARACTER SET ISO8859_1,
CONSTRAINT PK_TEST_SORTING PRIMARY KEY (PK)
);
SET TERM ^^ ;
CREATE TRIGGER TEST_SORTING_UCQ FOR TEST_SORTING ACTIVE BEFORE INSERT OR UPDATE
POSITION 0 AS
declare variable s varchar(50);
declare variable US varchar(50);
begin
if (new.MyString > '') then
begin
us = '';
s = upper(new.MyString);
while (s > '') do
begin
if (substring(s from 1 for 1) not in ('''', '"')) then
us = us || substring(s from 1 for 1);
s = substring(s from 2 for character_length(s));
end
new.stripped = us;
end
end ^^
SET TERM ; ^^
HTH,
Set
