Convert char to numeric value in Informix SQL
Hi, I have a query which I want to go something like:
select fw_year
from table
where fw_year[1,4] between 2002 and 2007
and fw_year <> "BLAH"
The fw_year column contains values like "2001/2002", "2002/2003" and one special value "BLAH". Currently, despite my exclusion of "BLAH" in the filter, I get a "Character to numeric conversion error" when trying to do a numeric comparison. Is there a function to convert my year value to numeric for the comparison?
select fw_year
from table
where fw_year[1,4] between 2002 and 2007
and fw_year <> "BLAH"
The fw_year column contains values like "2001/2002", "2002/2003" and one special value "BLAH". Currently, despite my exclusion of "BLAH" in the filter, I get a "Character to numeric conversion error" when trying to do a numeric comparison. Is there a function to convert my year value to numeric for the comparison?
Bill Bach
I'm not as familiar with Informix, but most SQL compilers use 'single quotes' to delineate strings, and "double-quotes" to indicate a way to bypass the SQL parser (for using odd field names like "Select"). Could it be as simple as changing "BLAH" to 'BLAH'?
ASKER
'Fraid not. Informix allows either. Thanks for the suggestion though!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did work (will give you some points for it), but unfortunately I was intending to expand the SQL to something like this:
select fw_year
from table
where fw_year[1,4] between (year(current)-5) and year(current)
and fw_year <> "BLAH"
which I don't think can work with that technique. Oracle has a function to_number which would do what I want, but I can't seem to find an informix equivalent.
select fw_year
from table
where fw_year[1,4] between (year(current)-5) and year(current)
and fw_year <> "BLAH"
which I don't think can work with that technique. Oracle has a function to_number which would do what I want, but I can't seem to find an informix equivalent.
ASKER
I may be able to create a stored procedure to do it, and convert the value with a LET, as suggested here:
http://www.dbforums.com/archive/index.php/t-1003223.html
the same page suggests a cast too, but that doesn't seem to work in my case. It makes me think the database engine is trying to convert the value "BLAH" to a number despite being excluded - I guess it would depend on which order the query optimizer chooses to apply the filters. I know you can give hints to the query optimizer, but I'm not sure I want to go there!
http://www.dbforums.com/archive/index.php/t-1003223.html
the same page suggests a cast too, but that doesn't seem to work in my case. It makes me think the database engine is trying to convert the value "BLAH" to a number despite being excluded - I guess it would depend on which order the query optimizer chooses to apply the filters. I know you can give hints to the query optimizer, but I'm not sure I want to go there!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Using "REPLACE" worked! Thanks very much!!!