Need some assistance in converting freeform physical memory values to KB in Oracle
I have a dataset in Oracle 11g which contains the physical memory values for some systems. However, the data was entered freeform, and I need to convert the values to a standard format in KB.
The function I came up with is:
Some examples of the memory values are:
There has to be an easier way to do this and get accurate results... It seems like when I convert back and forth between floating point numbers the decimal is ignored, so 2.5Gb =25Gb... I'm not sure if there is a library missing, or my logic is just messed up.
I'd appreciate any suggestions...
Thanks,
Jaffer
The function I came up with is:
create or replace FUNCTION CONVERTMEMORY
(
MEMORYSIZE IN VARCHAR2
) RETURN VARCHAR2 IS
v_tmpSize number:=0;
v_tmpMemory Varchar2(40):='';
r_tmpMemory varchar2(40):='';
v_sideA varchar2(20):='';
v_sideB varchar2(20):='';
BEGIN
select trim(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(upper(MEMORYSIZE),'NO RAM','0GB'),'HOT SPARE',''),' (8)256MB MEMORY DIMMS',''),'N/A','0GB'),'INTERNAL',''),'NA','0GB'),'RAM',''),'1GB 3/4','1.75GB'),',',''),'EDO',''),'NO RAM!!','0GB'),'1GB 1/2','1.5GB'),'PC133',''),'.',''),'MEMORY',''),'1 GB1','1GB'),'ONE GB','1GB'),'~',''),'DATA',''),'DDR 2',''),'DDR',''),'!',''),'0 TAKE FOR PROTECTOR','0GB'),'GFB','GB'),'GB MB','GB'),'TT# 449656','0GB'),'20-36GB','36GB'),'1.047.968','1047968'),'1048576','1GB'),'14GB+','14GB')) into v_tmpMemory from dual;
select trim(replace(v_tmpMemory,' ','')) into v_tmpMemory from dual;
if (instr(v_tmpMemory,'(')-1)>0 then
select trim(substr(v_tmpMemory,1,instr(v_tmpMemory,'(')-1)) into v_tmpMemory from dual;
end if;
select trim(replace(replace(v_tmpMemory,'&','+'),'AND','+')) into v_tmpMemory from dual;
-- Work the math
if ((instr(v_tmpMemory,'X')>0) and (instr(v_tmpMemory,'+')=0)) then
begin
select substr(v_tmpMemory,1,instr(v_tmpMemory,'X')-1) into v_sideA from dual;
select substr(v_tmpMemory,instr(v_tmpMemory,'X')+1) into v_sideB from dual;
select to_char(to_number(v_sideA,'9999999999.9')*to_number(CONVERTMEMORY(v_sideB),'9999999999.9'),'9999999999.9') into r_tmpMemory from dual;
EXCEPTION WHEN OTHERS THEN
select 'Unknown - ERROR('||v_tmpMemory||'-'||v_sideA||' * '||v_sideB||')' into r_tmpMemory from dual;
end;
elsif (instr(v_tmpMemory,'+')>0) then
begin
select substr(v_tmpMemory,1,instr(v_tmpMemory,'+')-1) into v_sideA from dual;
select substr(v_tmpMemory,instr(v_tmpMemory,'+')+1) into v_sideB from dual;
select to_char(to_number(CONVERTMEMORY(v_sideA),'9999999999.9')+to_number(CONVERTMEMORY(v_sideB),'9999999999.9'),'9999999999.9') into r_tmpMemory from dual;
EXCEPTION WHEN OTHERS THEN
select 'Unknown - ERROR('||v_tmpMemory||'-'||v_sideA||' - '||v_sideB||')' into r_tmpMemory from dual;
end;
elsif (upper(v_tmpMemory) like '%GB' and Upper(v_tmpMemory) NOT LIKE '%X%') then
begin
v_tmpMemory:=trim(replace(replace(Upper(v_tmpMemory),'GB',''),',',''));
v_tmpSize:=to_number(v_tmpMemory,'9999999999.9');
v_tmpSize:=v_tmpSize*1024;
select to_char(v_tmpSize,'9999999999.9') into r_tmpMemory from dual;
EXCEPTION WHEN OTHERS THEN
select 'Unknown - ERROR('||v_tmpMemory||')' into r_tmpMemory from dual;
end;
elsif (Upper(v_tmpMemory) like '%GIG' and Upper(v_tmpMemory) NOT LIKE '%X%') then
begin
v_tmpMemory:=trim(replace(replace(Upper(v_tmpMemory),'GIG',''),',',''));
v_tmpSize:=to_number(v_tmpMemory,'9999999999.9');
v_tmpSize:=v_tmpSize*1024;
select to_char(v_tmpSize,'9999999999.9') into r_tmpMemory from dual;
EXCEPTION WHEN OTHERS THEN
select 'Unknown - ERROR('||v_tmpMemory||')' into r_tmpMemory from dual;
end;
elsif (Upper(v_tmpMemory) like '%GIGS' and Upper(v_tmpMemory) NOT LIKE '%X%') then
begin
v_tmpMemory:=trim(replace(replace(Upper(v_tmpMemory),'GIGS',''),',',''));
v_tmpSize:=to_number(v_tmpMemory,'9999999999.9');
v_tmpSize:=v_tmpSize*1024;
select to_char(v_tmpSize,'9999999999.9') into r_tmpMemory from dual;
EXCEPTION WHEN OTHERS THEN
select 'Unknown - ERROR('||v_tmpMemory||')' into r_tmpMemory from dual;
end;
elsif (Upper(v_tmpMemory) like '%G' and Upper(v_tmpMemory) NOT LIKE '%X%') then
begin
v_tmpMemory:=trim(replace(replace(Upper(v_tmpMemory),'G',''),',',''));
v_tmpSize:=to_number(v_tmpMemory,'9999999999.9');
v_tmpSize:=v_tmpSize*1024;
select to_char(v_tmpSize,'9999999999.9') into r_tmpMemory from dual;
EXCEPTION WHEN OTHERS THEN
select 'Unknown - ERROR('||v_tmpMemory||')' into r_tmpMemory from dual;
end;
elsif (UPPER(v_tmpMemory) like '%MB' and Upper(v_tmpMemory) NOT LIKE '%X%') then
begin
v_tmpMemory:=trim(replace(replace(Upper(v_tmpMemory),'MB',''),',',''));
v_tmpSize:=to_number(v_tmpMemory,'999999999.99');
v_tmpSize:=v_tmpSize;
select to_char(v_tmpSize,'9999999999.9') into r_tmpMemory from dual;
EXCEPTION WHEN OTHERS THEN
select 'Unknown - ERROR('||v_tmpMemory||')' into r_tmpMemory from dual;
end;
elsif (UPPER(v_tmpMemory) like '%M' and Upper(v_tmpMemory) NOT LIKE '%X%') then
begin
v_tmpMemory:=trim(replace(replace(Upper(v_tmpMemory),'M',''),',',''));
v_tmpSize:=to_number(v_tmpMemory,'9999999999.9');
v_tmpSize:=v_tmpSize;
select to_char(v_tmpSize,'9999999999.9') into r_tmpMemory from dual;
EXCEPTION WHEN OTHERS THEN
select 'Unknown - ERROR('||v_tmpMemory||')' into r_tmpMemory from dual;
end;
elsif (UPPER(v_tmpMemory) like '%KB' and Upper(v_tmpMemory) NOT LIKE '%X%') then
begin
v_tmpMemory:=trim(replace(replace(Upper(v_tmpMemory),'KB',''),',',''));
v_tmpSize:=to_number(v_tmpMemory,'9999999999.9');
v_tmpSize:=v_tmpSize/1024;
select to_char(v_tmpSize,'9999999999.9') into r_tmpMemory from dual;
EXCEPTION WHEN OTHERS THEN
select 'Unknown - ERROR('||v_tmpMemory||')' into r_tmpMemory from dual;
end;
elsif (UPPER(v_tmpMemory) like '%K' and Upper(v_tmpMemory) NOT LIKE '%X%') then
begin
v_tmpMemory:=trim(replace(replace(Upper(v_tmpMemory),'K',''),',',''));
v_tmpSize:=to_number(v_tmpMemory,'9999999999.9');
v_tmpSize:=v_tmpSize/1024;
select to_char(v_tmpSize,'9999999999.9') into r_tmpMemory from dual;
EXCEPTION WHEN OTHERS THEN
select 'Unknown - ERROR('||v_tmpMemory||')' into r_tmpMemory from dual;
end;
else
begin -- Assume it's in MB
v_tmpSize:=to_number(v_tmpMemory,'9999999999.9');
v_tmpSize:=v_tmpSize;
select to_char(v_tmpSize,'9999999999.9') into r_tmpMemory from dual;
EXCEPTION WHEN OTHERS THEN
select 'Unknown - ERROR('||v_tmpMemory||')' into r_tmpMemory from dual;
end;
end if;
RETURN r_tmpMemory;
END CONVERTMEMORY;Some examples of the memory values are:
1
1
1 G
1 GB
1 GB (512+256+2x128)
1 GB(512+256+2x128)
1 GB1
1 GIG
1 Gb
1 Gig
1 g
1 gb
1 gig
1,024GB
1,047,968
1,048,084 KB
1,048,576
1,536MB
1.0 GB
1.047.968
1.2 GB
1.25
1.256GB
1.2GB
1.4GB
1.5 GB
1.5 GB EDO
1.5G
1.5GB
1.5GB PC133
1.5Gb
1.5gb
1.75GB
1.8gb
10
10 GB
1000
1000 MB
1024
1024
1024 GB
1024 MB
1024 Mb
1024 Mb (2 x 512)
1024 Mb (4 x 256)
1024M
1024MB
1024Mb
1024mb
1047
1048
1048092
1048104KB
1048576
1048576 KB
10GB
10GB RAM
112GB
1152MB
1152mb
1179648
12
12 GB
12 Gig
12 gb
120GB
128
128 GB
128 MB
128 MB RAM
128 Mb (2 x 64)
1280
128G
128GB
128Gb
128M
128MB
128mb
12G
12GB
12Gb
12gb
130
1300 MB
130484
1310
1310720
1310720KB
14 GB
14336 MB
144 GB
144GB
144Gb
14GB
14GB+
1536 MB
1536MB
1572864
16
16 GB
16 gb
16 gig
16.0 GB
160GB
163
16384 MB
16384MB
16G
16GB
16Gb
16MB
16gb
172 RAM
18 GB
18GB
192
192 GB
192GB
192MB
195
196MB
1G
1GB
1GB (4x256)
1GB 1/2
1GB 3/4
1GB MB
1GB(2X512)
1GB(4X256)
1GB(4x256)
1GB(8X128)
1GIG
1Gb
1Gb(4X256)
1g
1gb
1gb(4x256)
2
2 GB
2 GB (8)256MB Memory DIMMs
2 GB RAM
2 GIG
2 Gb
2 Gb (2x 1024mb)
2 Gb (4 x512Mb)
2 Gb (4x 512Mb)
2x9GB & 2x18GB Internal
4 GB + 1 GB Hot Spare
1
1 G
1 GB
1 GB (512+256+2x128)
1 GB(512+256+2x128)
1 GB1
1 GIG
1 Gb
1 Gig
1 g
1 gb
1 gig
1,024GB
1,047,968
1,048,084 KB
1,048,576
1,536MB
1.0 GB
1.047.968
1.2 GB
1.25
1.256GB
1.2GB
1.4GB
1.5 GB
1.5 GB EDO
1.5G
1.5GB
1.5GB PC133
1.5Gb
1.5gb
1.75GB
1.8gb
10
10 GB
1000
1000 MB
1024
1024
1024 GB
1024 MB
1024 Mb
1024 Mb (2 x 512)
1024 Mb (4 x 256)
1024M
1024MB
1024Mb
1024mb
1047
1048
1048092
1048104KB
1048576
1048576 KB
10GB
10GB RAM
112GB
1152MB
1152mb
1179648
12
12 GB
12 Gig
12 gb
120GB
128
128 GB
128 MB
128 MB RAM
128 Mb (2 x 64)
1280
128G
128GB
128Gb
128M
128MB
128mb
12G
12GB
12Gb
12gb
130
1300 MB
130484
1310
1310720
1310720KB
14 GB
14336 MB
144 GB
144GB
144Gb
14GB
14GB+
1536 MB
1536MB
1572864
16
16 GB
16 gb
16 gig
16.0 GB
160GB
163
16384 MB
16384MB
16G
16GB
16Gb
16MB
16gb
172 RAM
18 GB
18GB
192
192 GB
192GB
192MB
195
196MB
1G
1GB
1GB (4x256)
1GB 1/2
1GB 3/4
1GB MB
1GB(2X512)
1GB(4X256)
1GB(4x256)
1GB(8X128)
1GIG
1Gb
1Gb(4X256)
1g
1gb
1gb(4x256)
2
2 GB
2 GB (8)256MB Memory DIMMs
2 GB RAM
2 GIG
2 Gb
2 Gb (2x 1024mb)
2 Gb (4 x512Mb)
2 Gb (4x 512Mb)
2x9GB & 2x18GB Internal
4 GB + 1 GB Hot Spare
There has to be an easier way to do this and get accurate results... It seems like when I convert back and forth between floating point numbers the decimal is ignored, so 2.5Gb =25Gb... I'm not sure if there is a library missing, or my logic is just messed up.
I'd appreciate any suggestions...
Thanks,
Jaffer
I just re-read your question.. i think you are after this.. any value you enter.. you want that to give output in kb
This will accept any input , i had used upto GB's..
select convertmemorykb('1GB') from dual
and will return output in kb's
CREATE OR REPLACE FUNCTION convertmemorykb (
p_bytes IN VARCHAR2,
p_decimal IN NUMBER DEFAULT 0
)
RETURN VARCHAR2
AS
hbytes VARCHAR2 (8000);
v_bytes NUMBER;
BEGIN
v_bytes :=
REPLACE (REPLACE (REPLACE (UPPER (p_bytes), 'KB', ''), 'MB', ''),
'GB',
''
);
IF INSTR (UPPER (p_bytes), 'KB') > 0
THEN
RETURN p_bytes;
ELSIF INSTR (UPPER (p_bytes), 'MB') > 0
THEN
v_bytes := TO_NUMBER (v_bytes) * 1024;
RETURN v_bytes;
ELSIF INSTR (UPPER (p_bytes), 'GB') > 0
THEN
v_bytes := TO_NUMBER (v_bytes) * 1024 * 1024;
RETURN v_bytes;
END IF;
END;
/This will accept any input , i had used upto GB's..
select convertmemorykb('1GB') from dual
and will return output in kb's
I think you are going to have to combine the methods here.
Wasim has a function that will convert clean data for you. I don't see how it will work with "1 GB(512+256+2x128)".
What you need to do is clean the data. I don't think there is going to be a magic bullet here.
I believe that if you take the scrubbing that you are doing to the data and then use the function that Wasim has to do the conversion for you, you are going to be pretty close.
A way to remove the parts in parenthesis is:
select regexp_replace(v_tmpMemory
It appears that you are trying to do some odd substring to do that.
I ran your data through your filters and it seems that if you do a few more replaces (GIG->GB, G->GB and remove spaces) it should work pretty well in Wasim's function. Just be careful with the replace for G->GB (probably need REGEXP_REPLACE) so that you don't change GB->GGB.
Wasim has a function that will convert clean data for you. I don't see how it will work with "1 GB(512+256+2x128)".
What you need to do is clean the data. I don't think there is going to be a magic bullet here.
I believe that if you take the scrubbing that you are doing to the data and then use the function that Wasim has to do the conversion for you, you are going to be pretty close.
A way to remove the parts in parenthesis is:
select regexp_replace(v_tmpMemory
It appears that you are trying to do some odd substring to do that.
I ran your data through your filters and it seems that if you do a few more replaces (GIG->GB, G->GB and remove spaces) it should work pretty well in Wasim's function. Just be careful with the replace for G->GB (probably need REGEXP_REPLACE) so that you don't change GB->GGB.
Try this...
I made the assumption that if there are multiple dots then treat them as commas and hence ignore them for numerical purposes. I also made the assumption that any unlabeled number was already in KB.
Comparison of results
CREATE OR REPLACE FUNCTION parse_mem(p_str IN VARCHAR2)
RETURN NUMBER
IS
v_temp VARCHAR2(32767) := REPLACE(UPPER(REPLACE(p_str, ' ')), ',');
v_result NUMBER;
BEGIN
v_temp := REGEXP_REPLACE(v_temp, '\([^)]*\)|B|RAM');
v_temp := REPLACE(v_temp, 'GIG', 'G');
v_temp := REPLACE(v_temp, '&', '+');
v_temp := REPLACE(v_temp, 'X', '*');
v_temp := REGEXP_SUBSTR(v_temp, '.+G|.+M|.+K|[0-9.]+');
IF INSTR(
v_temp,
'.',
1,
2
) > 0
THEN
v_temp := REPLACE(v_temp, '.');
END IF;
v_temp := REPLACE(v_temp, 'G', '*1024*1024');
v_temp := REPLACE(v_temp, 'M', '*1024');
v_temp := REPLACE(v_temp, 'K');
SELECT XMLQUERY (v_temp RETURNING CONTENT).getnumberval() INTO v_result FROM DUAL;
RETURN v_result;
END;I made the assumption that if there are multiple dots then treat them as commas and hence ignore them for numerical purposes. I also made the assumption that any unlabeled number was already in KB.
Comparison of results
STRING PARSE_MEM(STRING) CONVERTMEMORY(STRING)
1 1 1.0
1 1 1.0
1 G 1048576 1024.0
1 GB 1048576 1024.0
1 GB (512+256+2x128) 1048576 1024.0
1 GB(512+256+2x128) 1048576 1024.0
1 GB1 1048576 1024.0
1 GIG 1048576 1024.0
1 Gb 1048576 1024.0
1 Gig 1048576 1024.0
1 g 1048576 1024.0
1 gb 1048576 1024.0
1 gig 1048576 1024.0
1,024GB 1073741824 1048576.0
1,047,968 1047968 1047968.0
1,048,084 KB 1048084 1023.5
1,048,576 1048576 1024.0
1,536MB 1572864 1536.0
1.0 GB 1048576 10240.0
1.047.968 1047968 1047968.0
1.2 GB 1258291.2 12288.0
1.25 1.25 125.0
1.256GB 1317011.456 1286144.0
1.2GB 1258291.2 12288.0
1.4GB 1468006.4 14336.0
1.5 GB 1572864 15360.0
1.5 GB EDO 1572864 15360.0
1.5G 1572864 15360.0
1.5GB 1572864 15360.0
1.5GB PC133 1572864 15360.0
1.5Gb 1572864 15360.0
1.5gb 1572864 15360.0
1.75GB 1835008 179200.0
1.8gb 1887436.8 18432.0
10 10 10.0
10 GB 10485760 10240.0
1000 1000 1000.0
1000 MB 1024000 1000.0
1024 1024 1024.0
1024 1024 1024.0
1024 GB 1073741824 1048576.0
1024 MB 1048576 1024.0
1024 Mb 1048576 1024.0
1024 Mb (2 x 512) 1048576 1024.0
1024 Mb (4 x 256) 1048576 1024.0
1024M 1048576 1024.0
1024MB 1048576 1024.0
1024Mb 1048576 1024.0
1024mb 1048576 1024.0
1047 1047 1047.0
1048 1048 1048.0
1048092 1048092 1048092.0
1048104KB 1048104 1023.5
1048576 1048576 1024.0
1048576 KB 1048576 Unknown - ERROR(1GB)
10GB 10485760 10240.0
10GB RAM 10485760 10240.0
112GB 117440512 114688.0
1152MB 1179648 1152.0
1152mb 1179648 1152.0
1179648 1179648 1179648.0
12 12 12.0
12 GB 12582912 12288.0
12 Gig 12582912 12288.0
12 gb 12582912 12288.0
120GB 125829120 122880.0
128 128 128.0
128 GB 134217728 131072.0
128 MB 131072 128.0
128 MB RAM 131072 128.0
128 Mb (2 x 64) 131072 128.0
1280 1280 1280.0
128G 134217728 131072.0
128GB 134217728 131072.0
128Gb 134217728 131072.0
128M 131072 128.0
128MB 131072 128.0
128mb 131072 128.0
12G 12582912 12288.0
12GB 12582912 12288.0
12Gb 12582912 12288.0
12gb 12582912 12288.0
130 130 130.0
1300 MB 1331200 1300.0
130484 130484 130484.0
1310 1310 1310.0
1310720 1310720 1310720.0
1310720KB 1310720 1280.0
14 GB 14680064 14336.0
14336 MB 14680064 14336.0
144 GB 150994944 147456.0
144GB 150994944 147456.0
144Gb 150994944 147456.0
14GB 14680064 14336.0
14GB+ 14680064 14336.0
1536 MB 1572864 1536.0
1536MB 1572864 1536.0
1572864 1572864 1572864.0
16 16 16.0
16 GB 16777216 16384.0
16 gb 16777216 16384.0
16 gig 16777216 16384.0
16.0 GB 16777216 163840.0
160GB 167772160 163840.0
163 163 163.0
16384 MB 16777216 16384.0
16384MB 16777216 16384.0
16G 16777216 16384.0
16GB 16777216 16384.0
16Gb 16777216 16384.0
16MB 16384 16.0
16gb 16777216 16384.0
172 RAM 172 172.0
18 GB 18874368 18432.0
18GB 18874368 18432.0
192 192 192.0
192 GB 201326592 196608.0
192GB 201326592 196608.0
192MB 196608 192.0
195 195 195.0
196MB 200704 196.0
1G 1048576 1024.0
1GB 1048576 1024.0
1GB (4x256) 1048576 1024.0
1GB 1/2 1048576 15360.0
1GB 3/4 1048576 179200.0
1GB MB 1048576 1024.0
1GB(2X512) 1048576 1024.0
1GB(4X256) 1048576 1024.0
1GB(4x256) 1048576 1024.0
1GB(8X128) 1048576 1024.0
1GIG 1048576 1024.0
1Gb 1048576 1024.0
1Gb(4X256) 1048576 1024.0
1g 1048576 1024.0
1gb 1048576 1024.0
1gb(4x256) 1048576 1024.0
2 2 2.0
2 GB 2097152 2048.0
2 GB (8)256MB Memory DIMMs 2097152 2048.0
2 GB RAM 2097152 2048.0
2 GIG 2097152 2048.0
2 Gb 2097152 2048.0
2 Gb (2x 1024mb) 2097152 2048.0
2 Gb (4 x512Mb) 2097152 2048.0
2 Gb (4x 512Mb) 2097152 2048.0
2x9GB & 2x18GB Internal 56623104 55296.0
4 GB + 1 GB Hot Spare 5242880 5120.0 ASKER
I think the problem that I'm struggling with twofold, first there are some entries like "2x9GB & 2x18GB" or "4 GB + 1 GB Hot Spare" so I need to perform some math in order to end up with the proper values; next is that there are many variants of the formatting and it doesn't appear that the RegExp libraries are loaded in the DB.
-Jaffer
-Jaffer
>>> I think the problem that I'm struggling with twofold, first there are some entries like "2x9GB & 2x18GB" or "4 GB + 1 GB Hot Spare"
The function I posted handles those. If the resulting KB from my function is not what you're expecting, please post what the results should be. At most I would expect a rounding error.
>>> it doesn't appear that the RegExp libraries
11g has regexp support.
What are you trying to do that is failing and what exactly is the failure?
The function I posted handles those. If the resulting KB from my function is not what you're expecting, please post what the results should be. At most I would expect a rounding error.
>>> it doesn't appear that the RegExp libraries
11g has regexp support.
What are you trying to do that is failing and what exactly is the failure?
You don't really even need to use a pl/sql function but I would just for cleanliness and reusability.
Here's and example using the same logic as my function, but all in sql. I ran this on an 11.2.0.3 database
For the sake of the example, I hardcoded the sample data into the query so it could be self-contained.
That is, you should be able to copy and paste it "as is" and run it.
You would of course, use your own columns and your own tables.
I modified the output to inclue KB, MB and GB to make it easier to see how rounding applies
Again, if any of the conversions are not correct, please list the original value along with the expected returned value and I'll make the adjustment. To my eye though, all of the math appears to be correct for each of the sample variations you provided, including the ones requiring additional math.
Here's and example using the same logic as my function, but all in sql. I ran this on an 11.2.0.3 database
For the sake of the example, I hardcoded the sample data into the query so it could be self-contained.
That is, you should be able to copy and paste it "as is" and run it.
You would of course, use your own columns and your own tables.
I modified the output to inclue KB, MB and GB to make it easier to see how rounding applies
SELECT original, kb, ROUND(kb/1024) mb, ROUND(kb/1024/1024) gb FROM
(SELECT original,
XMLQUERY (
CASE
WHEN INSTR(
x,
'.',
1,
2
) > 0
THEN
REPLACE(x, '.')
ELSE
x
END RETURNING CONTENT).getnumberval()
kb
FROM (SELECT original,
REPLACE(
REPLACE(
REPLACE(REGEXP_SUBSTR(
REPLACE(
REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REPLACE(UPPER(REPLACE(original, ' ')), ','),
'\([^)]*\)'
),
'GB|GIG',
'G'
),
'B|RAM'
),
'&',
'+'
),
'X',
'*'
),
'.+G|.+M|.+K|[0-9.]+'
),
'G',
'*1024*1024'
),
'M',
'*1024'
),
'K'
) x
FROM
( SELECT REGEXP_SUBSTR(
myvalues,
'[^|]+',
1,
LEVEL
)
original
FROM (SELECT '1|1|1 G|1 GB|1 GB (512+256+2x128)|1 GB(512+256+2x128)|1 GB1|1 GIG|1 Gb|1 Gig|1 g|1 gb|1 gig|1,024GB|1,047,968|1,048,084 KB|1,048,576|1,536MB|1.0 GB|1.047.968|1.2 GB|1.25|1.256GB|1.2GB|1.4GB|1.5 GB|1.5 GB EDO|1.5G|1.5GB|1.5GB PC133|1.5Gb|1.5gb|1.75GB|1.8gb|10|10 GB|1000|1000 MB|1024|1024|1024 GB|1024 MB|1024 Mb|1024 Mb (2 x 512)|1024 Mb (4 x 256)|1024M|1024MB|1024Mb|1024mb|1047|1048|1048092|1048104KB|1048576|1048576 KB|10GB|10GB RAM|112GB|1152MB|1152mb|1179648|12|12 GB|12 Gig|12 gb|120GB|128|128 GB|128 MB|128 MB RAM|128 Mb (2 x 64)|1280|128G|128GB|128Gb|128M|128MB|128mb|12G|12GB|12Gb|12gb|130|1300 MB|130484|1310|1310720|1310720KB|14 GB|14336 MB|144 GB|144GB|144Gb|14GB|14GB+|1536 MB|1536MB|1572864|16|16 GB|16 gb|16 gig|16.0 GB|160GB|163|16384 MB|16384MB|16G|16GB|16Gb|16MB|16gb|172 RAM|18 GB|18GB|192|192 GB|192GB|192MB|195|196MB|1G|1GB|1GB (4x256)|1GB 1/2|1GB 3/4|1GB MB|1GB(2X512)|1GB(4X256)|1GB(4x256)|1GB(8X128)|1GIG|1Gb|1Gb(4X256)|1g|1gb|1gb(4x256)|2|2 GB|2 GB (8)256MB Memory DIMMs|2 GB RAM|2 GIG|2 Gb|2 Gb (2x 1024mb)|2 Gb (4 x512Mb)|2 Gb (4x 512Mb)|2x9GB & 2x18GB Internal|4 GB + 1 GB Hot Spare'
myvalues
FROM DUAL)
CONNECT BY REGEXP_SUBSTR(
myvalues,
'[^|]+',
1,
LEVEL
)
IS NOT NULL)))Again, if any of the conversions are not correct, please list the original value along with the expected returned value and I'll make the adjustment. To my eye though, all of the math appears to be correct for each of the sample variations you provided, including the ones requiring additional math.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked great, I added a few additional replacements to remove some junk that wasn't included in my sample set (like ~12GB, remove the ~). And thank you for reminding me about XMLQuery!
Thank you for your assistance!
-Jaffer
Thank you for your assistance!
-Jaffer
This is one of the fucntions from the oracle forums
https://community.oracle.com/thread/3572933?start=0&tstart=0
I had modified it a bit so as to accept the parameters in the free flow like kb,mb,gb
you have to add in replace clause if you are going beyond that..
Open in new window
illustration is
select convertmemory('1214 kB',3) from dual
it gives output as 1.186 MB
select convertmemory('912',3) from dual
output is 912 bytes
you may have to tweak it a bit further depending on your needs