Conversion of Imperial to Metric System in SQL Server 2008

There will be case when you need to convert the values in Imperial system i.e. in feet and inches to the one in metric system. The conversion logic seems simple, but when it comes to SQL Server, it takes a bit complex.

Here is the function which can convert imperial unit of measurement to the metric system.

CREATE FUNCTION [dbo].[fncImperialToMetric]

(

	@ImperialInput VARCHAR(50)

)

RETURNS FLOAT

AS

BEGIN

	-- Declare the return variable here

	DECLARE @Result FLOAT;

	DECLARE @myFeet as varchar(50);

	DECLARE @myInch as varchar(50);

	

	

	set @ImperialInput = RTRIM(ltrim(@ImperialInput));


	set @myFeet='';

	-- SEPARATE FEET AND INCH SECTION

	-- GET THE FEET SECTION

	if (CHARINDEX('''', @ImperialInput)-1)>0

		set @myFeet = SUBSTRING(@ImperialInput,1,CHARINDEX('''', @ImperialInput)-1);


	--GET THE INCH SECTION	

	set @myInch = REPLACE(@ImperialInput,@myFeet ,'');

	set @myInch = REPLACE(@myInch,'''' ,'');

	set @myInch = REPLACE(@myInch,'"' ,'');

	

	set @Result = cast(@myFeet as float) * 12 + CAST( @myInch as float);

	set @Result = cast(cast(@Result as int)* 0.0254 as numeric(6,2));


	Return @Result;

Run the following SQL Query to test the results:-

The results are:-