SQL, parse string like "12M" to 12 * 30 as an int

Question

Hi,

In a stored procedure in SQL Server 2008, I need to parse strings like "12M" and return 12 * 30 days as an int. So, I am basically parsing and calculating the number of days the string represents.

I am not sure how this can be done.

I am thinking to do a while loop over each character in the string. Any suggestion?

Edit (not author): It appears the goal is to convert "xM" to "x Months in days" (30 days/month), "yY" to "y Years in days" (365days/year), and "zD" to "z Days" (no transformation).

Answer 1

Yes, do NOT do it in SQL. Put in a .NET based stored procedure for that stuff - you will love being able to properly debug it easily, test it and have access ot the date manupulation classes.

Answer 2

Use:

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

For testing:

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

Returns:

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

Addendum

These values should be stored separately, as two columns:

• value INT
• value_type CHAR(1) (D, M, Y)