In Microsoft SQL Server function I have variables declared as float datatype

Declare @variable1 float, @variable2 float, @variable3 float;

I am storing following value in my variable1

SET @variable1 = DATALENGTH(@variable2) - DATALENGTH(@variable3);

As DATALENGTH stores results in Byte.. In the above case what will be the datatype of result stored in @variable1

What will be the impact on datatypes of these variables

I need to migrate the Length() functionality from Oracle to SQL Server

Thanks for the help !

CodePudding user response：

Your question title says...

Is DATALENGTH() in SQL Server substitute for LENGTH() in Oracle

Suggesting you have a working query in Oracle, that you'd like to run in SQL Server. To answer that part alone, would be as follows...

• LEN() would be the SQL Server equivalent of LENGTH from Oracle.
• DATALENGTH would be the SQL Server equivalent of LENGTHB from Oracle.

Both accept input as a string. LEN() will give you the length of a string by character count, whereas DATELENGTH will give you the length of a string by byte count. For example, in the case of nvarchar data, your byte length would be double your character length. This is because nvarhcar uses 2 bytes to store one character instead of 1 byte for varchar.

However, your examples use FLOAT data types. On the surface, it seems to be acceptable to use LEN or DATALENGTH, as the FLOAT value is implicitly converted to a string, and you get a result you expect.

However, as Jeroen Mostert points out, this may not always be the case...

To see why this is potentially a problem, or at least needlessly confusing without an explicit conversion, consider LEN(0.0000001E0), which is 6, since the implicit conversion gives 1e-007. LENGTH(0.0000001E0) in Oracle is 8.