• Post Reply Bookmark Topic Watch Topic
  • New Topic

to_number function question.

 
Fritz Largosa
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Sheriff
Posts: 36019
427
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fritz,
To do that in a non-database specific way, you can use a union:


In some databases, like Oracle, you can use a function for the conditional.
 
Happiness is not a goal ... it's a by-product of a life well lived - Eleanor Roosevelt. Tiny ad:
the new thread boost feature: great for the advertiser and smooth for the coderanch user
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!