I have a database column which type is bytea. It contains floats converted as byte array (4 bytes per one float) and encoding is Escape. I'm be able to retrieve corresponding bytea string using substring function.
My question is how can I convert bytea string to float inside a SQL function. Earlier I converted to float in C# side. I used dataReader.getByte method to retrieve bytes and then Converted to float using BitConverter.ToSingle (.Net build in class) method.
Now I can't use intermediate component as Npqsql driver. I want SQL to directly convert bytea into floats and return the corresponding number when execute a query from 3rd party application.
Thanks Amila
For this purpose the best possible solution is to convert into bytes using IEEE754-1985 standard using SQL commands.
First it is required to check for special cases defined by IEEE754-1985 standard. Then just follow the standard algorithm to convert if it is not in any special cases. Sample code is below.
Inputs are bytea_value bytea, is_little_endian boolean
then divide into 4 bytes as below:
byte_array[0]:= get_byte(bytea_value, 0);
byte_array[1]:= get_byte(bytea_value, 1);
byte_array[2]:= get_byte(bytea_value, 2);
byte_array[3]:= get_byte(bytea_value, 3);
Then get the binary value by considering little endian or big endian
IF is_little_endian THEN
binary_value:= byte_array[0]::bit(8) || byte_array[1]::bit(8) || byte_array[2]::bit(8) || byte_array[3]::bit(8);
ELSE
binary_value:= byte_array[3]::bit(8) || byte_array[2]::bit(8) || byte_array[1]::bit(8) || byte_array[0]::bit(8);
END IF;
Now check for special cases:
IF binary_value = '00000000000000000000000000000000' OR binary_value = '10000000000000000000000000000000' THEN -- IEEE754-1985 Zero
return 0.0;
END IF;
sign := substring(binary_value from 1 for 1);
exponent := substring(binary_value from 2 for 8);
mantissa := substring(binary_value from 10 for 23);
IF exponent = '11111111' THEN
IF mantissa = '00000000000000000000000' THEN -- IEEE754-1985 negative and positive infinity
IF sign = '1' THEN
return '-Infinity';
ELSE
return 'Infinity';
END IF;
ELSE
return 'NaN'; -- IEEE754-1985 Not a number
END IF;
END IF;
If it does not belong to any special cases just convert it as below:
exp := exponent::int;
IF exp > 126 THEN
exp := exp - 127;
ELSE
exp:= -exp;
END IF;
WHILE mantissa_index < 24 LOOP
IF substring(mantissa from mantissa_index for 1) = '1' THEN
result := result + power(2, -(mantissa_index));
END IF;
mantissa_index = mantissa_index + 1;
END LOOP;
result := result * power(2, exp);
IF(sign = '1') THEN
result = -result;
END IF;
return result;