sql - CHARINDEX - 从 nvarchar 中挑选日期

Question

我正在尝试在下面的代码中添加“天”。字符串是沿着

• 1天3小时4分15秒
• 23 小时 3 分 4 秒
• 4分1秒
• 4 小时 3 分 1 秒
• 5天3小时1分0秒

每次我尝试一些我认为会起作用的东西时，它都会在其他东西上出错。下面的代码有效。我只需要在其中添加“天”。

SELECT *,
    CASE WHEN CHARINDEX('hrs',REPLACE([Session Duration],' ',''),0) >0 
            THEN SUBSTRING(REPLACE([Session Duration],' ',''),0,CHARINDEX('hrs',REPLACE([Session Duration],' ',''),0))
            ELSE 0 END AS Hrs,
    CASE WHEN CHARINDEX('hrs',REPLACE([Session Duration],' ',''),0) >0  AND CHARINDEX('min',REPLACE([Session Duration],' ',''),0) >0
            THEN SUBSTRING(REPLACE([Session Duration],' ',''),CHARINDEX('hrs',REPLACE([Session Duration],' ',''),0)+3,CHARINDEX('min',REPLACE([Session Duration],' ',''),0) - CHARINDEX('hrs',REPLACE([Session Duration],' ',''),0)-3)
         WHEN CHARINDEX('min',REPLACE([Session Duration],' ',''),0) >0
            THEN  SUBSTRING(REPLACE([Session Duration],' ',''),0,CHARINDEX('min',REPLACE([Session Duration],' ',''),0))
        ELSE 0 END AS mins,
    CASE WHEN CHARINDEX('min',REPLACE([Session Duration],' ',''),0) >0  AND CHARINDEX('sec',REPLACE([Session Duration],' ',''),0) >0
            THEN SUBSTRING(REPLACE([Session Duration],' ',''),CHARINDEX('min',REPLACE([Session Duration],' ',''),0)+3,CHARINDEX('sec',REPLACE([Session Duration],' ',''),0) - CHARINDEX('min',REPLACE([Session Duration],' ',''),0)-3)
         WHEN CHARINDEX('sec',REPLACE([Session Duration],' ',''),0) >0
            THEN  SUBSTRING(REPLACE([Session Duration],' ',''),0,CHARINDEX('sec',REPLACE([Session Duration],' ',''),0))
        ELSE 0 END AS secs
FROM table1

标签： sqltsql

Answer 1

下面的查询将返回Days，如果字符串中没有days，Session Duration它将返回 0。

SELECT ...,
       ...,
       CASE WHEN CHARINDEX('days', REPLACE([Session Duration], ' ', ''), 0) > 0 
       THEN SUBSTRING(REPLACE([Session Duration], ' ', ''), 0, CHARINDEX('days', REPLACE([Session Duration], ' ', ''), 0))
       ELSE 0 END AS [Days],
      ...
FROM Table1