Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator.The calculators can be used to add, subtract, multiply or divide fractions. Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. So take your time and evaluate the problem before you let others know how easy or hard it will be to solve. This is true with a lot of computing problems. Looks can be deceivingĪlthough the function in this article isn’t very complicated, it shows that sometimes solutions are more complex than we think. To call my new function, I can execute the following script: SELECT dbo.udf_ConvertToFraction('5.234') Once these calculations are complete, I am done and can construct the final fractional value. I repeat the same step, except this time I check to see if both numbers are evenly divisible by 5. First, if the numerator and denomiator are even numbers, I continue dividing them by 2 until they are reduced as far as they can be, or one of them is no longer even. To do this, I use the modulo operator (%) to determine the remainder after the division. The next step is to reduce the fraction to its lowest terms. This value is calculated by determining the number of decimal places to the right of the decimal point. Next, I need to determine the multiple to use for my numerator and denominator. SET = CASE WHEN > 0 THEN CONVERT(VARCHAR, ELSE '' END + ' ' + CONVERT(VARCHAR, + '/' + CONVERT(VARCHAR, = first thing the udf_ConvertToFraction function does is store the input value as separate whole number and decimal number values. continue reducing numerator and denominator until one AS long AS both numbers are even numbers, keep reducing them. The functionīelow is the script for the user-defined function that accepts a decimal value and converts that number to its fractional representation. This becomes increasingly complicated when presented with more complex decimals, such as 5.234327. However, you then need to put the fraction into its lowest terms. 25 by 100, which gives you the fraction 25/100. When converting from a decimal value to a string representation of the fraction of that value, it becomes a bit more complex.įor example, if you need to convert the decimal 5.25 to the fraction 5 ¼, where do you start to do the conversion? The mathematical rule is to multiply. You can typically use the built-in CAST or CONVERT functions to do the conversions for you. Converting numbersĬonverting numbers to or from string values in SQL Server is fairly simple. Here’s how you can write a TSQL user-defined function to convert a floating point decimal number into a fractional representation of the same number. At first glance, it seemed reasonably simple, but after digging into the actual code a bit, I realized it was rather challenging. I recently worked on an interesting problem that involved converting a decimal number to a fraction value in SQL Server. Convert numbers to fractions using SQL ServerĭBA Tim Chapman shows how to write a TSQL user-defined function to convert a floating point decimal number into a fractional representation of the same number.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |