However, while not being interested into db design issues or similar,
I think we could do so with stored procedure :
something like (syntax not checked) :
create procedure MY_SP (INPUT_STRING varchar(100))
returns MY_NUMERIC NUMERIC(18,2)
begin
MY_NUMERIC = INPUT_STRING;
when convert_error do
MY_NUMERIC = NULL;
suspend;
end

And then query would look :
select * from xxx
where

(
(SELECT MY_NUMERIC FROM MY_SP( xxx.THIS_DATE_FIELD )) > 1975
)


It wont be so fast though :), but no need to write udf :).

Adomas


Martijn Tonies wrote:

>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
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>

--

Pagarbiai / Best Regards,
Adomas Urbanavicius
+37068543336