Last week I wrote about using TRANSLATE to detect numbers in data Using The TRANSLATE() function...
Andrew Clarke at Radio Free Tooting pointed out the shortcomings of using TRANSLATE() to detect numbers.
As I said earlier, all I needed to do was detect if the characters in a string were all digits or not, and I wanted it to be very fast.
But Andrew's remarks got me thinking - could translate be used to detect more complex numbers?
Here's the short list of requirements:
* Detect integers
* Detect numbers with decimal point ( 4.6, 0.2, .7)
* Detect negative and positive ( leading + or - )
* Reject text with more than 1 '.', such as an IP address ( 127.0.0.1 )
* Reject anything with alpha text
As part of a presentation I'm preparing, I would like to get an idea of RMAN usage in the Oracle Community.
If you can spare a couple of minutes, please fill out this 10 question multiple choice survey: RMAN Usage Survey
Update:
It seems I made a poor choice of online Survey site.
To get more than 100 responses to this survey, a "Professional" version must be purchased. At a rate of 1 or 2 surveys a year (for me) that is not exactly practical.
Sharing the Survey results also requires the Professional version.
Here's a summary of the results in MS Excel.
A few small enhancements to exception handling/error messages. June 2004
I read this post by Laurent Schneider yesterday. In the comment section Tom Kyte already explained what the issue was about, but I’ll expand this explanation a little.
The question was why should the apparently invalid statement below work? I mean there is no such column nor number as “1.x”), yet the statement works ok:
SQL> select 1.x from dual; X ---------- 1
The column header gives a good hint what happened above. Oracle has treated the X as the column alias.
Let’s remove the “x” and see:
SQL> select 1. from dual; 1. ---------- 1
Now all works as expected, “1.” is treated as number ( 1. = 1.0 )
I read this post by Laurent Schneider yesterday. In the comment section Tom Kyte already explained what the issue was about, but I’ll expand this explanation a little.
The question was why should the apparently invalid statement below work? I mean there is no such column nor number as “1.x”), yet the statement works ok:
SQL> select 1.x from dual; X ---------- 1
The column header gives a good hint what happened above. Oracle has treated the X as the column alias.
Let’s remove the “x” and see:
SQL> select 1. from dual; 1. ---------- 1
Now all works as expected, “1.” is treated as number ( 1. = 1.0 )
I read this post by Laurent Schneider yesterday. In the comment section Tom Kyte already explained what the issue was about, but I’ll expand this explanation a little.
The question was why should the apparently invalid statement below work? I mean there is no such column nor number as “1.x”), yet the statement works ok:
SQL> select 1.x from dual; X ---------- 1
The column header gives a good hint what happened above. Oracle has treated the X as the column alias.
Let’s remove the “x” and see:
SQL> select 1. from dual; 1. ---------- 1
Now all works as expected, “1.” is treated as number ( 1. = 1.0 )
I read this post by Laurent Schneider yesterday. In the comment section Tom Kyte already explained what the issue was about, but I’ll expand this explanation a little.
The question was why should the apparently invalid statement below work? I mean there is no such column nor number as “1.x”), yet the statement works ok:
SQL> select 1.x from dual; X ---------- 1
The column header gives a good hint what happened above. Oracle has treated the X as the column alias.
Let’s remove the “x” and see:
SQL> select 1. from dual; 1. ---------- 1
Now all works as expected, “1.” is treated as number ( 1. = 1.0 )
Oracle has evolved over time to much more than just a plain relational database. One option is to use Oracle as an expensive calculator.
When researching or demoing Oracle, it’s quite convenient to do number calculations directly on sqlplus prompt, especially if dealing with internals where lots of stuff is about addresses and offsets shown in hex.
Here’s the script what I use for such purposes: https://github.com/tanelpoder/tpt-oracle/blob/master/calc.sql.
It usually saves me couple of seconds every calculation as I don’t have to reopen the calc.
Oracle has evolved over time to much more than just a plain relational database. One option is to use Oracle as an expensive calculator.
When researching or demoing Oracle, it’s quite convenient to do number calculations directly on sqlplus prompt, especially if dealing with internals where lots of stuff is about addresses and offsets shown in hex.
Here’s the script what I use for such purposes: https://github.com/tanelpoder/tpt-oracle/blob/master/calc.sql.
It usually saves me couple of seconds every calculation as I don’t have to reopen the calc.
Oracle has evolved over time to much more than just a plain relational database. One option is to use Oracle as an expensive calculator.
When researching or demoing Oracle, it’s quite convenient to do number calculations directly on sqlplus prompt, especially if dealing with internals where lots of stuff is about addresses and offsets shown in hex.
Here’s the script what I use for such purposes: https://github.com/tanelpoder/tpt-oracle/blob/master/calc.sql.
It usually saves me couple of seconds every calculation as I don’t have to reopen the calc.
Recent comments
3 years 3 days ago
3 years 12 weeks ago
3 years 16 weeks ago
3 years 17 weeks ago
3 years 22 weeks ago
3 years 43 weeks ago
4 years 11 weeks ago
4 years 41 weeks ago
5 years 25 weeks ago
5 years 26 weeks ago