I was wondering if anyone can help? In our database we have a field for an age. Its a open field and anyone can type anything into it.So we get 2, 2 years, 2Yrs, 6, 6 months, 6mths, 1.5weeks, 3.5months old, 3 days, 15months etc etc etc.

We tried to get it limited to put years and month in etc but will not be done by IT.

Is there any code that you guys can help with that will convert all the above to years?

There is no clean way to accomplish what you are trying to do. You have to ask, beg, plead, cajole, bribe, threaten or do something else to get your IT guys to restrict the entry into the field so it is always entered in a consistent format. Ideally, like Chandu suggested, have them enter a birthdate.

If you want to clean up the existing data it is painful - you will need to do it in multiple steps. See the example below, which is only partial. You can copy and paste this to an SSMS window and run it to see what it does.

You could create a table to hold the set of abbreviations you'll accept for time (e.g., ('Yrs', 'Years'), ('Y', 'Years'), ('Mnths', 'Months'), etc) and join it to your raw data based on some string matching logic. That way you could translate the raw data into an age ('24Mnths' -> 2 Years). I can only imagine that you'll need to iterate through this process until you find every variation/permutation/mutation of strings being input by the end user. You have my sympathies...

=================================================The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

For example, the code below pulls out the values for the days and/or weeks and/or months and/or years. To me, CROSS APPLY makes the code easier to write and to understand, and thus to maintain.

The last code needed would be to check the values for numeric and add them to get (approximate) years. I need some more details on what you need in order to write that code:? How to treat non-numeric/invalid values ?? Do you want a final decimal #years, such as 3.2 or 0.2? If not, how/when to round? If yes, how many decimal places and how to round ?etc..

For example, the code below pulls out the values for the days and/or weeks and/or months and/or years. To me, CROSS APPLY makes the code easier to write and to understand, and thus to maintain.

The last code needed would be to check the values for numeric and add them to get (approximate) years. I need some more details on what you need in order to write that code:? How to treat non-numeric/invalid values ?? Do you want a final decimal #years, such as 3.2 or 0.2? If not, how/when to round? If yes, how many decimal places and how to round ?etc..

You might need to add something to add the case where just a number without any indicator as to whether it is years, months, or something else is entered. Couple of rows in the OP's examples fall into that category.

That is just from casual observation. Who knows what other kind of data/pattern can be there in the data. After all it is free form data, according to the OP.

This will work fine when both Number and alphabet(i.e., Days or Months or Years) were mentioned in Age_Column.If only Number is mentioned then you can add the above solution (i.e which is mentioned by ScottPletcher) to my solution.

SELECT CASE WHEN Age_Column LIKE '%Y%' THEN Age_Column WHEN Age_Column LIKE '%M%' THEN LEFT((CAST(AGE_COLUMN AS VARCHAR)+'.0') /12.0,3) WHEN Age_Column LIKE '%D%' THEN LEFT((CAST(AGE_COLUMN AS VARCHAR)+'.0') /365.0,3) WHEN Age_Column NOT LIKE ('%Y%','%D%','%M%') THEN Age_Column) ELSE '' END AS 'Years'