At 02:16 AM 12/03/2006, you wrote:

> > What is in the column? And what do you want to have as
> > the result?
>
>I'm dealing with a search feature for a data-entry where all the fields are
>just textual fields.
>The only thing I know about them is their length.
>
>When the user knows that he's been treating one field as a numeric field, he
>might want to test it with a function like that.
>If a field represents a year, they can enter things like
> "before 1976",
> "after 313",
> "circa 1999"
> "2005",
> "1976",
> "850".
>
>Saying something like
> WHERE THIS_DATE_FIELD > 1950
>I'd like the server, if possible, to return all the fields that match the
>criterium, where applicable.
>I can of course do it with a UDF, but I just wondered whether it'd be
>possible with the SQL extensions of Firebird.

As Martijn commented, what you want is silly, i.e. illogical. If it
is important for a value to be handled as a numeric then you store it
in a numeric structure. A string is not a numeric structure. Even
if your *user* decided to store only numbers in a char or varchar
field, you would have to cast everything to get a valid numeric comparision.

> > Gimme all rows where AFIELD is small than 20, except
> > when it's a value that cannot be converted, then do, eh,
> > well, whatever.
>
>I did not invent either CASE or COALESCE, did I?

Fortunately for the SQL world! :-)

Computer languages -- including SQL -- not natural languages with
varying syntaxes that depend on context. And databases are
abstractions of real-world information, stored in such a way that the
*information* they contain can be retrieved without loss of meaning.

By storing numbers in strings, you remove the meaning from the
information. That's OK if the numbers are never going to be needed
for anything that is meaningful about numbers.

You can extract *some* meaning from your strings by searching a
specific constant number, e.g. where aString CONTAINING '1976', but
an ad hoc string is otherwise not meaningful for evaluations.

Writing a UDF will be complicated and error-prone, even when you do
develop a better understanding of how the search predicates work,
i.e. the logic of retrieval.

>The fact that the language does not help a user to implement such thing,
>doesn't mean the language is poor.

The language is adequate to the task that it is designed for. But
the design of the database must be adequate to the tasks that it is
designed for, too. The database designer has to have a thorough
understanding of how data is going to be used and design the storage
structures accordingly. I understand you are an IT student....well,
don't bunk the lectures about requirements taking and analysis.

>However, neither does it necessarily mean that's a silly request to ask...
>;)

<sundaysermon>

Pragmatically, it is a silly request to ask. Love it or hate it, if
you reach the point in the creation process where you have stored
data that is insufficiently abstract to meet the requirements, you
are facing a new iteration of requirements, analysis and design.

Creating database systems iteratively is NOT silly. It's only silly
if you get to the point where you are, discover your mistake, and try
to solve it by creating a Band-Aid (like writing a UDF to extract
numeric meaning from the contents of an ad hoc string).

And note, I'm not telling you that writing a UDF to extract numeric
meaning from the contents of a string is always a design
error. Requirements sometimes dictate that formal collections of
characters be stored in a string of fixed or variable length. Every
character is meaningful, position in the string is meaningful, even
the length of the string may be meaningful: to extract the meaning
from such strings, you have to know what these characters and
sequences mean (abstraction) and analyse what the retrieval requirements are.

Once you know that, then you move into design. At that point (no
later!) you have to develop algorithms for extracting the information
from the string and test your algorithms rigorously. It may well be
that a UDF exists already for implementing this algorithm, e.g.
barcodes, phonetic search values, etc., or you might need to roll
your own UDF; or you might find that designing a stored procedure
will be the solution. This is part of the design process, not
something that you should be confronting at the pointy end of implementation!

All that should help to clarify what "silly" means in the context of
your current question. Don't be hurt by it. Learn from the
experience and apply the principles that you should be attending to
on the days when your lectures are not in the computer lab!

Out here in Big Rude World, I meet far too many bright young fellows
who scheduled basketball on all the days when they had lectures about
the "blunt end" of the development process. They flunked, or
marginally passed, the non-lab courses. They are hopeless in
software teams. You can recognise them easily during the design
phase. While everyone else is furiously scribbling with pencil and
paper, these types sit for hours in front of a screen that has
nothing on it except

CREATE DATABASE

</sundaysermon>

./heLen