SQLTeam.com | Weblogs | Forums

How convert oracle select function to sql server

Hi All.
I found select with Oracle function but I need similar for SQL Server. Is possible to convert it to SQL Server select?

SELECT 
weight, 
trunc(weight) as kg, 
nvl(substr(weight - trunc(weight), 2), 0) as gms
FROM mass_table;

Thanks.

To convert to T-SQL you need to first understand what the Oracle SQL is doing...so what is the trunc function doing and what is the nvl function doing?

You should also evaluate what nvl(substr(weight - trunc(weight), 2), 0) is doing...once you understand that then you can determine what functions in T-SQL will return the same results.

Looking at this - you have a weight in decimal format and you want to break that out to separate pieces. The easiest way to get the value left of the decimal is to convert to integer - to get the value to the right of the decimal depends on how you want the result.

For example - if you want gms to be 0.nnn then you can use: weight % 1
If you want an integer value for gms - you can use the precision of weight (for example - decimal(5,2) - you can then use: cast(weight % 1 * 100 as int)

There are other options...depends on the results you want.

Hi jeffw8713. Thanks for reply.
I need to get result in three column:

  1. original value;
  2. kg as int i.e. part of original value without decimal after point;
  3. gms as int but part of original after decimal point.

For example:
weight
5.67
34.567
365.253
34

result

weight........kg....gms
5.67.............5......67
34.567.......34....567
365.253...365....253
34..............34........0
Thanks

have you looked at the answers provided to you here?

What is the data type of the weight column? The fact that you have values with different precision tells me that it is a string (varchar).

But further to that - the value .67 converted to an integer as 67 and the value .567 converted to the integer 567 will be viewed incorrectly - because .67 is actually greater than .567 but will be viewed as less than which is not correct.

To be correct - you need to parse out the decimal to the same precision for all values. That can be done easily enough by multiplying the result by 1000 (3 decimal places = 670) or 10000 (4 decimal places = 6700).

I would recommend leaving the decimal place for gms - it is more accurate than trying to convert the value to an integer.