在SQL Server中解析未验证的小数(varchar)

SQL Server 2005。我希望在不久的将来,当我们准备部署新应用程序时,可以运行一个脚本。我们有很多旧数据必须在新应用程序中移动到新表中。

其中一组数据是技术人员输入的采样小时数,在新应用程序中必须转换为秒数。

听起来很简单,但要振作起来。。。旧应用程序 没有验证 . 新列将是bigint,但旧列是varchar。查看我几个小时以来遇到的不同类型的数据。

24小时
24小时
24小时
22.3小时
24小时
不适用
3.09
19
86394

24:00有效时间:19:07
24小时/有效=13:44
15:8(有效=15:07)

好的,深呼吸,其实不算太糟。我已经完成了大部分艰苦的工作(识别用户一直在使用的各种模式)。这就是迄今为止所做的:

CREATE FUNCTION HoursToSecond(@input nvarchar(5))
RETURNS bigint
AS
BEGIN
    DECLARE @return bigint
    SELECT @return = CASE
        WHEN ISNUMERIC(@input)=1
        THEN CASE
                WHEN CAST(@input AS decimal(9,3))<100
                THEN CAST(@input as decimal(9,3))*3600 --convert hrs to secs
                ELSE CAST(@input as bigint) --already in seconds
            END
        WHEN CHARINDEX(':',@input)>0
        THEN CAST(left(@input,CHARINDEX(':',@input)-1) as int)*3600 +  
             CAST(SUBSTRING(@input,CHARINDEX(':',@input)+1,2) as int)*60
        ELSE NULL
    END
    RETURN @return
END

然后根据我在数据中看到的模式进行切换。

INSERT INTO NewDatasheets (sample_time)
    SELECT 
        CASE
           WHEN ISNUMERIC(samplingtime)=1 THEN dbo.HoursToSecond(samplingtime)
           WHEN samplingtime LIKE '% %' THEN dbo.HoursToSecond(LEFT(samplingtime, CHARINDEX(' ',samplingtime)-1))
           WHEN samplingtime LIKE '%h%' THEN dbo.HoursToSecond(LEFT(samplingtime, CHARINDEX('h',samplingtime)-1))
           WHEN samplingtime LIKE '%:%' THEN dbo.HoursToSecond(samplingtime)
           ELSE NULL
        END
    FROM OldDatasheets

难看的剧本工作。对我甚至没有尝试解析“valid”之后的小时数。但它可以完成90%的工作。我可以查询边缘案例并手动清理这些案例。。。但我想避免任何体力劳动。

我想知道是否有人有更好的解决方案,也许代码行更少,或者避免创建函数。