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 @Feet			INTEGER,
              @Inches			DECIMAL (6,2),
              @Inches_Integer		INTEGER,
              @Height_Formatted		VARCHAR (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