to_number function question.

[Fritz Largosa]

I have a sql query which converts my varchar to a number. Whenever there is a field which contains non numerical values. the query returns an error. Is there some way to return null if the field contains non-numerical data, and a number if the field contains numerical data?

my query: "select to_number(col_1) from tbl_a"

[Jeanne Boyarsky]

Fritz,
To do that in a non-database specific way, you can use a union:

select to_number(col_1) from tbl_a where col_1 is not null
union all
select col_1 from tbl_a where col_1 is null

In some databases, like Oracle, you can use a function for the conditional.