Hello Rony,

> > 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

Isn't this different from what you're asking? Or is the actual value
in the column "before 1976" and not a search criteria?

> I'd like the server, if possible, to return all the fields that match the
> criterium, where applicable.

And it will, depending on what you're asking :-)

Let me state one thing first -- if you're going to store a "year" value,
store it in an integer/numeric based datatype, not in a string-based
datatype. Why? Well, for example, because of the problems you're
experiencing -- 1976 > 200, but in a string, comparison works
very different.

> I can of course do it with a UDF, but I just wondered whether it'd be
> possible with the SQL extensions of Firebird.
>
> > 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?

See above, if the value holds "before 1976", what do you
expect the server to return for a WHERE clause of:
mycolumn < 2000

> > Could be me, but why? Isn't this a rather silly question
> > to ask from a server?
>
> The fact that the language does not help a user to implement such thing,
> doesn't mean the language is poor.
> However, neither does it necessarily mean that's a silly request to ask...
> ;)

I'm trying to understand the "problem" as such and figure out
what the best thing you can do is with the current language/server.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com