I have a table with a column of patient height data, stored as varchar type, in SQL Server.

Values are stored in this format:

There are instances of bad data too, such as 70, 5 ft 9 in, but that is far from the norm, some instances of 4'5" which I've used an initial query to replace the ' with ft and " with in to normalize.

What I need to do is convert the data into just numeric format, my output will have two columns, one for the actual numeric value, and the other for units.

The issue that I'm running into is that I get this error:

Error converting data type varchar to numeric.

Here's what I have attempted thus far, which clearly isn't working.

CASE 
    WHEN newvalue LIKE '%ft%in%' 
        THEN CONVERT(DECIMAL(10, 2), TRIM(LEFT(newvalue, PATINDEX('%f%', newvalue) - 1)))
    WHEN newvalue LIKE '%ft%' 
        THEN CONVERT(DECIMAL(10, 2), TRIM(LEFT(newvalue, PATINDEX('%f%', newvalue) - 1)))
    ELSE 0
END AS [ft value]

The end goal of course is to do something like [ft value]*12 [in value] for instances where the data is in feet and inches.

Any help appreciated!

CodePudding user response：

Just an option (without much testing)

Declare @YourTable Table ([SomeCol] varchar(50))
Insert Into @YourTable Values 
 ('60')
,('5 ft')
,('65 in')
,('5 ft 10 in')
,('4 ft 5.25 in')
     
Select A.SomeCol
      ,B.InInches
 From @YourTable A
 Cross Apply (
                Select InInches = sum( value) 
                  From (
                        Select value = try_convert(decimal(10,4),value)
                                       * case when lead(value,1) over (order by [key]) like '%ft%' then 12 else 1 end 
                         From  OpenJSON( '["' replace(string_escape(SomeCol,'json'),' ','","') '"]' )
                       ) B1
             ) B

Resutls

SomeCol         InInches
60              60.0000
5 ft            60.0000
65 in           65.0000
5 ft 10 in      70.0000
4 ft 5.25 in    53.2500

More Performant, but has more Risk

Select A.*
      ,InInches = (Pos1*Pos2)  (Pos3*Pos4)
 From @YourTable A
 Cross Apply ( 
                Select Pos1 = coalesce(try_convert(decimal(10,4),JSON_VALUE(S,'$[0]')),1.0)
                      ,Pos2 = coalesce(try_convert(decimal(10,4),JSON_VALUE(S,'$[1]')),1.0)
                      ,Pos3 = coalesce(try_convert(decimal(10,4),JSON_VALUE(S,'$[2]')),0.0)
                      ,Pos4 = coalesce(try_convert(decimal(10,4),JSON_VALUE(S,'$[3]')),0.0)
                 From  ( values ( '["' replace(replace(replace([SomeCol],'ft','12'),'in','1'),' ','","') '"]' )  ) B1(S)
             ) B