|Subject||Re: [firebird-support] VarChars sometimes begin with single or double quote|
|Author||Svein Erling Tysvær|
>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.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:
>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.
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);
if (new.MyString > '') then
us = '';
s = upper(new.MyString);
while (s > '') do
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));
new.stripped = us;
SET TERM ; ^^