Decimal2Text Measurement Function

Keith, 2003-06-27

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