Oracle: Converting a string to a number
For a customer, I was converting an Access database to an Oracle relational structure. As it turned out, several of the Access number fields were strings and the users had entered number with errors (example: 12.900.000) and text (example: UNLIMITED). So I put together a Sql statement to display non-numbers so the users could correct the corrupt data. I didn't want to make any assumptions. So I came up with the following: SELECT tbl.columnValidating FROM tableName tbl WHERE TRANSLATE(tbl.columnValidating,'~,$0123456789','~') IS NOT NULL AND tbl.columnValidating <> 'UNLIMITED' AND tbl.columnValidating <> 'any other text you want to filter out'