Technical Article

Decimal2Text Measurement Function

,

This function is based on a script by Tim Dietrich (tim@timdietrich.us) named format_height.sql. His function took what he called a decimal representation of a measurement and formatted it as feet and inches. In reality the input value was just a measurement formatted to look like a decimal number but it was not a true decimal number.

It is more likely that you would find a measurement stored in a DB field as a true decimal value and have to convert and format it as this function does.

For example, a measurement of 5 feet 9 inches, which would be passed in as the decimal 5.75, would be returned as 5'9".

Similarly, a measurement of 4 feet 11 inches, which would be passed in as the decimal 4.90, would be returned as 4'9".
   

/****************************************************************************************

FileName:
   format_height2.sql

Description:
   Contains SQL used to create the "Format_Height2" function.

   This function takes a decimal representation of a height value and returns a properly formatted string value. 

   For example, a measurement of 5 feet 9 inches, which would be passed in as the decimal 5.75, would be returned as 5'9". 

   Similarly, a measurement of 4 feet 11 inches, which would be passed in as the decimal 4.90, would be returned as 4'9".

History:
   06/26/2003 -- Keith A. Bay (kabay@wi.rr.com) 
   Initial implementation.

Notes:
   This function is based on a script by Tim Dietrich (tim@timdietrich.us) named format_height.sql.

****************************************************************************************/

CREATE FUNCTION dbo.Format_Height2 (@Height DECIMAL (6,2))
   RETURNS VARCHAR (6)
AS
   BEGIN

      DECLARE @FeetINTEGER,
              @InchesDECIMAL (6,2),
              @Inches_IntegerINTEGER,
              @Height_FormattedVARCHAR (6)

      SET @Feet = CAST(@Height AS INTEGER)

      SET @Inches = round(((@Height - @Feet) * 100)*120/1000,0)

      SET @Inches_Integer = CAST(@Inches AS INTEGER)

      SET @Height_Formatted = CAST(@Feet AS VARCHAR) + '''' 

      SET @Height_Formatted = @Height_Formatted + CAST(@Inches_Integer AS VARCHAR) + '"'

      RETURN @Height_Formatted

   END

Rate

Share

Share

Rate