CHARINDEX -挑出nvarchar的日子

Question

我试图在下面的代码中添加“days”。这条线是沿着……的方向

• 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

Answer 1

下面的查询将返回Days，如果Session Duration字符串中没有days，则返回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

Answer 2

我不确定这是最好的答案，但我这样做了：

declare @table table (data nvarchar(30))
insert into @table 
values
('1 sec'),
('4 min 1 sec'),
('4 hrs 3 min 1 sec'),
('23 hrs 3 min 4 sec'),
('1 days 3 hrs 4 min 15 sec'),
('1 days 23 hrs 59 min 59 sec'),
('5 days 3 hrs 1 min 0 sec'),
('532 days 3 hrs 1 min 0 sec')

select 
    data,
    CONVERT(int,LTRIM(SUBSTRING(data, PATINDEX('%[ 0-9][0-9] sec', data),PATINDEX('% sec', data)-PATINDEX('%[ 0-9][0-9] sec', data)+1))) secs,  
    CONVERT(int,LTRIM(SUBSTRING(data, PATINDEX('%[ 0-9][0-9] min%', data),PATINDEX('% min%', data)-PATINDEX('%[ 0-9][0-9] min%', data)+1))) mins,
    CONVERT(int,LTRIM(SUBSTRING(data, PATINDEX('%[ 0-9][0-9] hrs%', data),PATINDEX('% hrs%', data)-PATINDEX('%[ 0-9][0-9] hrs%', data)+1))) hrs,
    CONVERT(int,SUBSTRING(data, 0,PATINDEX('% days%', data))) days
from
    @table

输出：

​

​

不过，老实说，我的主要建议是，在将这些信息插入数据库表之前，首先不要将这些信息存储在不同的列中，并尝试将值拆分到单独的列中。如果这样做，处理表的速度将更快，而且它还会占用更少的磁盘空间，因为整数比字符串占用的空间更少。