Could you pls explain a little
January 3, 2003 - 10am Central time zone
Reviewer: A reader
Thanx much for you answer Could you pls explain briefly the steps here.
Why do y do that ascii and why that -64.
I am trying to understand your code by running individual peices.
Thanx much
Followup January 3, 2003 - 11am Central time zone:
first part:
2
decode(length(replace(translate(str,'0123456789','0000000000'),'0','')),
3 NULL, str, substr(str,1,length(str)-1) ) ||
the replace(translate()) trick simply
a) turns all digits into 0
b) turns all 0 into nothing
if the result of that is NULL -- the string is just a number (return str)
if the result of that is not null -- return the string minus the last character
so 100 returns 100
100A returns 100
Next:
4 to_char(
decode(length(replace(translate(str,'0123456789','0000000000'),'0','')),
5 NULL, 0,
6 ascii(substr(str,length(str)))-64), 'fm00')
looks at the string again.
If NULL -- then return 0, no digits.
else, knowing that A is ascii 65, B is ascii 66 and so on, we take the last character and get it's
ascii code, subtract 64 from it and that returns 1 for A, 2 for B and so on.
Format that as a 2 digit number with leading zeros and NO blank for the sign (thats what fm00 does)
Concatenate the two and you have your desired output
You are unbelievable
January 3, 2003 - 11am Central time zone
Reviewer: A reader
Thanx once again .
How do you do that is a mystery.
Excellent
January 3, 2003 - 5pm Central time zone
Reviewer: li from Vancouver, Canada
Excellent as always!
number to character
January 28, 2003 - 4am Central time zone
Reviewer: Abdal-Rahman Agha from Yemen
For the following:
select instr(to_char(9.25),'.') from dual; -- returns 2
BUT
select instr(to_char(9.00),'.') from dual; -- returns 0
how could we let the second one return 2 also?
Followup January 28, 2003 - 7am Central time zone:
select instr('9.00','.') from dual;
or
select instr(to_char(9.00,'fm9.00'),'.') from dual;
select instr(to_char(9.00),'.') from dual is EXACTLY the same as
select instr(to_char(9),'.') from dual;
the .00 is just there for us humans -- it is not part of a number, it is a format only. A number
never actually has 9.00 -- it has 9 and nothing more.
Extract the fraction portion
January 28, 2003 - 10am Central time zone
Reviewer: Abdal-Rahman Agha from Yemen
Thanks Tom for you response, BUT
Let us say I have a column of type number, and I need to extract the number after the decimal
point. The length of the number varies, may be 12, or 123409.2 or 999.123 or 123456789.12345, what
ever. So, how can I expect to put the format 'fmt....' in the 'To_char' function?
Followup January 28, 2003 - 2pm Central time zone:
select number-trunc(number)
from t
there you go, everything after the decimal. don't use a string operation for a mathematical
function.
Simpler solution
January 28, 2003 - 4pm Central time zone
Reviewer: Mike Madland from Bellevue, WA USA
If all you want to do is sort, why not just:
SQL> r
1 select str
2 from mytab
3 order by translate(str,'0123456789','0123456789'),
4* translate(str,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ')
STR
-----
100
100A
100B
11
11A
321
321A
7 rows selected.
Followup January 28, 2003 - 4pm Central time zone:
but the str = translate(str,'0123456789','0123456789')
??
so it doesn't sort right?? you left off something after the comma -- so I'm not sure of your whole
idea but I think you were trying:
ops$tkyte@ORA920> select str, translate(str,'0123456789','0123456789')
2 from t
3 order by translate(str,'0123456789','0123456789'),
4 translate(str,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ')
5 /
STR TRANS
----- -----
100 100
100A 100A
100B 100B
11 11
112 112
11A 11A
321 321
321A 321A
8 rows selected.
Now, note that I stuck 112 in there
ops$tkyte@ORA920>
ops$tkyte@ORA920> select str,
2 decode(length(replace(translate(str,'0123456789','0000000000'),'0','')),
3 NULL, str, substr(str,1,length(str)-1) ) ||
4 to_char(
5 decode(length(replace(translate(str,'0123456789','0000000000'),'0','')),
6 NULL, 0,
7 ascii(substr(str,length(str)))-64), 'fm00')
8 from t
9 /
STR DECODE(L
----- --------
100 10000
100A 10001
100B 10002
11 1100
11A 1101
112 11200
321 32100
321A 32101
8 rows selected.
And see where my 112 is vs yours?
Follow up
January 28, 2003 - 4pm Central time zone
Reviewer: Mike Madland from Bellevue, WA USA
Sorry, my
translate(str,'0123456789','0123456789')
was in my brain actually a
translate(str,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789')
but I posted too quickly I think.
My idea is simply to take the string and sort by the numeric part first then sort by the character
part:
1 select str,
2 to_number(translate(str,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
3 '0123456789')) num,
4 translate(str,'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
5 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') str2
6 from mytab
7 order by to_number(translate(str,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
8 '0123456789')),
9 NVL(translate(str,'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
10* 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),CHR(1))
STR NUM STR2
----- ---------- -----
11 11
11A 11 A
100 100
100A 100 A
100B 100 B
112 112
321 321
321A 321 A
8 rows selected.
If you add an order by on your decode with a to_number:
1 select str,
2 decode(length(replace(translate(str,'0123456789','0000000000'),'0','')),
3 NULL, str, substr(str,1,length(str)-1) ) ||
4 to_char(
5 decode(length(replace(translate(str,'0123456789','0000000000'),'0','')),
6 NULL, 0,
7 ascii(substr(str,length(str)))-64), 'fm00')
8 from t
9 order by to_number(
10 decode(length(replace(translate(str,'0123456789','0000000000'),'0','')),
11 NULL, str, substr(str,1,length(str)-1) ) ||
12 to_char(
13 decode(length(replace(translate(str,'0123456789','0000000000'),'0','')),
14 NULL, 0,
15 ascii(substr(str,length(str)))-64), 'fm00')
16* )
STR DECODE(L
----- --------
11 1100
11A 1101
100 10000
100A 10001
100B 10002
112 11200
321 32100
321A 32101
8 rows selected.
We get the same answer. The original poster wanted "to sort these columns on a numeric sort Order"
and this seems to do just that.
Followup January 28, 2003 - 7pm Central time zone:
yes, that'll work :)
String Stings
January 28, 2003 - 11pm Central time zone
Reviewer: Rich from NJ
Another way of doing it?
1 select str,
2 decode(ltrim(str, '1234567890'), NULL, str || '00',
3 substr(str, 1, instr(str, ltrim(str, '1234567890')) - 1)
4 || to_char(ascii(ltrim(str, '1234567890')) - 64, 'fm00'))
5 from rj
6 order by to_number(decode(ltrim(str, '1234567890'), NULL, str || '00',
7 substr(str, 1, instr(str, ltrim(str, '1234567890')) - 1)
8* || to_char(ascii(ltrim(str, '1234567890')) - 64, 'fm00')))
SQL> /
STR DECODE(L
----- --------
11 1100
11A 1101
11B 1102
11C 1103
100 10000
100A 10001
100B 10002
100C 10003
110 11000
110A 11001
110B 11002
110C 11003
321A 32101
321B 32102
321C 32103
Amazed !
January 29, 2003 - 1am Central time zone
Reviewer: ram from india
the way you explain it seems to make it very simple ! thanks for the wonderful site and book . i am
amazed by your knowledge !
Function returning a leading "<space>".
August 6, 2003 - 8pm Central time zone
Reviewer: ht from California
Tom,
Besides doing a "select ltrim(function_name) from dual", is there a way to remove the leading " "
when running a function that returns a number? This is causing issues when setting the results to
a ksh env variable.
My ksh script calls other functions that return varchar2s and the leading space is removed by "set
pagesize 0".
Optimally, I would like to return no leading spaces when calling a function that returns a number
without using ltrim in the function call.
Here's my code:
set echo on;
create or replace package testpkg1
as
function testfunc1
return number;
end;
/
show errors;
create or replace package body testpkg1
as
function testfunc1
return number
as
l_controlfileseq number;
begin
select controlfile_sequence# into l_controlfileseq from v$database;
return ltrim(l_controlfileseq);
end testfunc1;
end testpkg1;
/
show errors;
exit;
Here's the results:
select testpkg1.testfunc1 x from dual;
X
----------
59290
1 row selected.
select ltrim(testpkg1.testfunc1) x from dual
X
--------------------------------------------------------------------------------
59290
Thanks in advance.
Followup August 9, 2003 - 3pm Central time zone:
short of having your function return a STRING instead?
you can
a) use ltrim to convert to a string implicitly and then remove the default single space in the
front that comes from saving a space for a sign.
b) return a string that is "good" already -- don't return a number. eg: return to_char(
l_controfileseq,'fm9999999999999' ) instead.
c) use to_char in the select
ops$tkyte@ORA920> select 1234, to_char(1234,'fm99999999' ) from dual;
1234 TO_CHAR(1
---------- ---------
1234 1234
d) use dbms_output instead of selecting it
ops$tkyte@ORA920> set serveroutput on
ops$tkyte@ORA920> set verify off
ops$tkyte@ORA920> set embedded on
ops$tkyte@ORA920> set feedback off
ops$tkyte@ORA920> exec dbms_output.put_line( testpkg1.testfunc1 );
46668
ops$tkyte@ORA920>
as some choices.
August 9, 2003 - 10pm Central time zone
Reviewer: A reader
Thanks Tom - that's what I thought. I'll probably return a varchar2 instead of a number to avoid
having my code break if my <space> gets removed for some reason.
August 26, 2003 - 3pm Central time zone
Reviewer: gre
Tom,
How to extract a number 3.2 to 00320 if the number type
is number(5,2). I use the repacle(to_char(a,'000.00'),'.',null). But there is a balnk or null
behind this. SURPRISE! if i don't want to use function to connect sub sting ,what is the easiest
way ? (oracle 9)
THANKS!
Followup August 27, 2003 - 7am Central time zone:
ops$tkyte@ORA920LAP> create table t ( x number(5,2) );
Table created.
ops$tkyte@ORA920LAP> insert into t values ( 3.2 );
1 row created.
ops$tkyte@ORA920LAP> select '"' || to_char( x, 'fm000v00' ) || '"' from t;
'"'||TO_
--------
"00320"
Using Replace to give you desired characters, rather than remove undesired characters.
November 3, 2004 - 6pm Central time zone
Reviewer: Ben Ballard from Somerville, MA USA
I needed to remove anything that wasn't a 0-9 digit from a PL/SQL VARCHAR2 variable, and didn't
know how to do it easily until I read this page. One of Tom's answers inspired this:
l_result := replace(l_result,replace(translate(l_result, '0123456789','0000000000'),'0',''), '');
This would also work in a select statement. Essentially we first eliminate the digits, and take
what's left and then eliminate that from the original string. It's easier and more comprehensive
than trying to guess all the bad characters that could be there and directly replacing those with
blanks.
I'm using this to parse a business id number from an email, which is preceded by a known string,
but may be limited by anything, and may contain dashes and spaces thrown in the middle. A more
common use for this would be if you had a "phone number" field which was never validated or
controlled, so users could enter anything, and of course they will always come up with unexpected
ways of entering the number. Instead of trying to predict every possible variation, or go
character by character to check if it is a digit, you can just use this to get the digits.
|