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/m开发者_高级运维onth), "yY" to "y Years in days" (365days/year), and "zD" to "z Days" (no transformation).
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)
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.
精彩评论