Ashok's Programming World

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:-

SELECT [dbo].fncImperialToMetric ('5'' 10"'),
  [dbo].fncImperialToMetric ('5'' 2'),
  [dbo].fncImperialToMetric ('5'''),
  [dbo].fncImperialToMetric ('10'), 
  [dbo].fncImperialToMetric ('10"')

The results are:-