Could you pls explain a little
A reader, January 03, 2003 - 10:26 am UTC
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
January 03, 2003 - 11:09 am UTC
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
A reader, January 03, 2003 - 11:29 am UTC
Thanx once again .
How do you do that is a mystery.
Excellent
li, January 03, 2003 - 5:17 pm UTC
Excellent as always!
number to character
Abdal-Rahman Agha, January 28, 2003 - 4:25 am UTC
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?
January 28, 2003 - 7:30 am UTC
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
Abdal-Rahman Agha, January 28, 2003 - 10:54 am UTC
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?
January 28, 2003 - 2:05 pm UTC
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
Mike Madland, January 28, 2003 - 4:00 pm UTC
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.
January 28, 2003 - 4:08 pm UTC
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
Mike Madland, January 28, 2003 - 4:49 pm UTC
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.
January 28, 2003 - 7:56 pm UTC
yes, that'll work :)
String Stings
Rich, January 28, 2003 - 11:34 pm UTC
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 !
ram, January 29, 2003 - 1:00 am UTC
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>".
ht, August 06, 2003 - 8:20 pm UTC
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.
August 09, 2003 - 3:38 pm UTC
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.
A reader, August 09, 2003 - 10:48 pm UTC
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.
gre, August 26, 2003 - 3:52 pm UTC
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!
August 27, 2003 - 7:52 am UTC
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.
Ben Ballard, November 03, 2004 - 6:57 pm UTC
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.
natural sort order string comparision
Ravi B, February 03, 2012 - 2:28 am UTC
Hi Tom,
I have a specific requirement that i have to compare different software versions (greater than, less than, between) etc.
For example, I have software version strings that I want to compare. I want "1.2.10.5" to be considered greater than "1.2.9.1" or 11.0.0256.84 less than 11.0.3287.98 etc
As you know there could be different formats of software versions for different manufacturers/products. Assuming that the versions are numeric (not alpha numeric) separated by periods(dots), is it possible to write any such generic function/procedure which can compare the strings?
Thanks,
Ravi
February 03, 2012 - 10:08 am UTC
sort order string
Ravi B, February 03, 2012 - 11:10 am UTC
Excellent! This is exactly what I was looking for.
Thanks!!
conditional substr()
Praveen Ray, March 18, 2012 - 12:53 pm UTC
Hi Tom,
I want a sub-string from a string where end point is </this> and the start point is the first '>' in the direction right-to-left. For example, if the given string is '><787>590075</this>><787>5' then the result is '590075'.
Thank you.
Praveen
March 19, 2012 - 10:10 am UTC
check out:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions150.htm#i1239858 or, if you are like me and go old school (less cpu almost for sure)
ops$tkyte%ORA11GR2> variable x varchar2(30)
ops$tkyte%ORA11GR2> exec :x := '><787>590075</this>><787>5';
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select substr( txt, instr( txt, '>', -1 )+1 )
2 from (
3 select substr( :x, 1, instr( :x, '</this>' )-1 ) txt
4 from dual
5 );
SUBSTR(TXT,INSTR(TXT,'>',-1)+1)
--------------------------------
590075
ops$tkyte%ORA11GR2>
A slightly different One
Jose Tuttu, April 26, 2012 - 11:05 pm UTC
By going through these solutions, I was able to solve my problem, which was slightly different.
I had to sort like,
1
1A
1B
2
2A
10
11
20
300
301
So I did
SELECT str,
TO_NUMBER( TRANSLATE(str, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')) as numpart,
TRANSLATE(str, '0123456789', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as strpart
FROM T
ORDER BY numpart ASC NULLS FIRST, strpart ASC NULLS FIRST