sql-server 从varchar列提取数字以进行计算

prdp8dxp 于 2022-10-31 发布在其他

关注(0)|答案(1)|浏览(174)

我在SQL Server中有一个表，其中有一列患者身高数据，存储为varchar类型。
值以以下格式存储：
| <tab_name> |
| - -|
| 六十个|
| 5英尺|
| 65英寸|
| 5英尺10英寸|
| 4英尺5.25英寸|
也有坏数据的示例，如70，5英尺9英寸，但这远远不是标准，一些4英尺5英寸的示例，我已经使用初始查询将'替换为英尺，将“替换为英寸，以进行标准化。
我需要做的是将数据转换为数值格式，我的输出将有两列，一列用于实际数值，另一列用于单位。
我遇到的问题是，我收到以下错误：
将数据类型varchar转换为numeric时出错。
以下是我迄今为止所做的尝试，显然没有效果。

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]

最终目标当然是做类似于[ft value]*12 + [in value]的事情，例如数据是以英尺和英寸为单位的。
感谢您的帮助！

sql-server

来源：https://stackoverflow.com/questions/74080283/extracting-numbers-from-varchar-column-for-calculations

1条答案

按热度按时间

nqwrtyyt1#

只是一个选项（无需过多测试）

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

结果

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

性能更高，但风险更大

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

赞(0）回复(0）举报 2022-10-31

我来回答

sql-server 从varchar列提取数字以进行计算

1条答案

相关问题

热门标签

最新问答