SQL,将类似“12M”的字符串解析为12*30作为int

使用:

DECLARE val INT

SET val = CASE UPPER(RIGHT(column, 1))
            WHEN 'Y' THEN
              CAST(SUBSTRING(column, 1, LEN(column)-1) AS INT) * 365
            WHEN 'M' THEN
              CAST(SUBSTRING(column, 1, LEN(column)-1) AS INT) * 30
            WHEN 'D' THEN
              CAST(SUBSTRING(column, 1, LEN(column)-1) AS INT) * 1
          END

用于测试:

WITH sample AS (
  SELECT '12M' AS [column]
  UNION ALL
  SELECT '100M'
  UNION ALL
  SELECT '10000M'  
  UNION ALL
  SELECT '1D'
  UNION ALL
  SELECT '34D'
  UNION ALL
  SELECT '2343M' )
SELECT s.[column],
       CASE UPPER(RIGHT(s.[column], 1))
         WHEN 'Y' THEN
           CAST(SUBSTRING(s.[column], 1, LEN(s.[column])-1) AS INT) * 365
         WHEN 'M' THEN
           CAST(SUBSTRING(s.[column], 1, LEN(s.[column])-1) AS INT) * 30
         WHEN 'D' THEN
           CAST(SUBSTRING(s.[column], 1, LEN(s.[column])-1) AS INT) * 1
       END
  FROM sample s

col      result
-----------------
12M      360
100M     3000
10000M   300000
1D       1
34D      34
2343M    70290

附录

这些值应分别存储为两列:

• 数值类型字符(1)(D、M、Y)

是的,不要用SQL。把它放在一个基于.NET的存储过程中——你会喜欢能够很容易地正确调试它,测试它,并且能够访问日期操作类。