Age convertor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Age convertor

Author

Topic

rachew
Starting Member

4 Posts

Posted - 2013-08-22 : 04:20:14

Morning,

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?

Thanks for your help.

Rich

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-22 : 04:39:44

Why are you storing AGE... Its better to have Date of Birth column with DATE....
any way provide us the sample data which you have presently....

--
Chandu

rachew
Starting Member

4 Posts

Posted - 2013-08-22 : 04:51:59

Hi Chandu,

Its an emergency call centre, so sometimes the caller doesn't know the DOB.

13 Years
72Y
57
78 Y
Unknown
89 Y
99 Years
17 Years
42 Y
45 Years
31 Years
42 Years
32 Years
9 Months
Unknown Ra
86 Years
Unknown Ra
Unknown Ra
47 Years
25 Years
89 Years

32 Years
1 Years
NULL
34 Years
40 Years
Unknown Ra
15 Years
50 Years
Unknown Ra
58 Years
6 W
NULL
NULL
NULL
25 Years
90 Years
81 Years
29 Y
Unknown
96 Years
95 Years
87 Years
56 Years
69 Years
20 Years
37 Years
62 Years
Unknown Ra
81 Years
25 Years
38 Years
77 Years
73 Y
21 Years
28Y
36 Years
89 Years
6 Y
90 Years
19 Years
29 Y
24 Years
43 Years
32 Y
39 Years
87 Years
Unknown Ra
79 Y
98 Y
80 Y
67 Years
22 Y
46 Y
Unknown
Unknown
Unknown Ra
34 Years
90 Y
84 Y
74
83 Years
48 Years
4 Years
Unknown Ra
80Y
92 Y
20 Years
19 Years
89 Y
40 Years
Unknown
12 Years
76 Years
79 Y
54 Years
Unknown Ra
86 Years
60 Years
78 Years
25 Years
90 Years
64 Years
37 Years
48 Years
55 Y
Unknown Ra
57 Years
62 Years
26 Years
99 Years
85 Years
49 Y
67 Years
34 Years
55 Years
44 Years
50 Years
72 Y
82 Y
48 Years
57 Years
84
Unknown
Unknown
30 Years
52 Y
22 Years
71 Y
1.5W
52 Years
101 Years
23 Years
Unknown Ra
42 Years
32 Years
20 Years
59 Years
20 Y
44 Y
33 Years
72 Years
77 Years
21 Years
85 Years
72YR
29Y
23 Years
89 Y
80 Years
17 Years
18 Years
18 Years
89 Years
19 Years
89 Y
76 Years
70 Y
Unknown Ra
20 Years
46 Y
25 Years
Unknown
74 Years
Unknown
65 Years
49 Years
95 Y
21 Years
93 Y
34Y
Unknown
98 Y
45 Years
20 Years
29 Years
25 Years
80 Years
Unknown Ra
90 Years
20 Years
96 Years
89 Y
49 Years
57 Years
Unknown
78 Years
50 Years
88 Years
29 Years
57 Y
89 Years
48 Years
96 Years
80 Years
27 Years
25 Years
21 Years
62Y
NULL

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-22 : 07:24:04

How you will get to know the years/months....
if there is 20 years 10 months, 30 Y, 12 years, 400 weeks etc.....
what should be the output?

--
Chandu

rachew
Starting Member

4 Posts

Posted - 2013-08-22 : 08:06:39

Hi Chandu, the output needs to be years.
So anything which is days/weeks/months needs to be in the range of 0-12months as '0'years.
Anything which is between month 12-24 then '1'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-22 : 08:24:41

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.

CREATE TABLE #tmp(randomText VARCHAR(256));
INSERT INTO #tmp VALUES
('13 Years'),
('72Y'),
('57'),
('78 Y'),
('Unknown'),
('89 Y'),
('99 Years'),
('17 Years'),
('42 Y'),
('45 Years'),
('31 Years'),
('42 Years'),
('32 Years'),
('9 Months'),
('Unknown Ra'),
('86 Years'),
('Unknown Ra'),
('Unknown Ra'),
('47 Years'),
('25 Years'),
('89 Years'),
(''),
('32 Years'),
('1 Years'),
(NULL),
('34 Years'),
('40 Years'),
('Unknown Ra'),
('15 Years'),
('50 Years'),
('Unknown Ra'),
('58 Years'),
('6 W'),
(NULL),
(NULL),
(NULL),
('25 Years'),
('90 Years'),
('81 Years'),
('29 Y'),
('Unknown'),
('96 Years'),
('95 Years'),
('87 Years'),
('56 Years'),
('69 Years'),
('20 Years'),
('37 Years'),
('62 Years'),
('Unknown Ra'),
('81 Years'),
('25 Years'),
('38 Years'),
('77 Years'),
('73 Y'),
('21 Years'),
('28Y'),
('36 Years'),
('89 Years'),
('6 Y'),
('90 Years'),
('19 Years'),
('29 Y'),
('24 Years'),
('43 Years'),
('32 Y'),
('39 Years'),
('87 Years'),
('Unknown Ra'),
('79 Y'),
('98 Y'),
('80 Y'),
('67 Years'),
('22 Y'),
('46 Y'),
('Unknown'),
('Unknown'),
('Unknown Ra'),
('34 Years'),
('90 Y'),
('84 Y'),
('74'),
('83 Years'),
('48 Years'),
('4 Years'),
('Unknown Ra'),
('80Y'),
('92 Y'),
('20 Years'),
('19 Years'),
('89 Y'),
('40 Years'),
('Unknown'),
('12 Years'),
('76 Years'),
('79 Y'),
('54 Years'),
('Unknown Ra'),
('86 Years'),
('60 Years'),
('78 Years'),
('25 Years'),
('90 Years'),
('64 Years'),
('37 Years'),
('48 Years'),
('55 Y'),
('Unknown Ra'),
('57 Years'),
('62 Years'),
('26 Years'),
('99 Years'),
('85 Years'),
('49 Y'),
('67 Years'),
('34 Years'),
('55 Years'),
('44 Years'),
('50 Years'),
('72 Y'),
('82 Y'),
('48 Years'),
('57 Years'),
('84'),
('Unknown'),
('Unknown'),
('30 Years'),
('52 Y'),
('22 Years'),
('71 Y'),
('1.5W'),
('52 Years'),
('101 Years'),
('23 Years'),
('Unknown Ra'),
('42 Years'),
('32 Years'),
('20 Years'),
('59 Years'),
('20 Y'),
('44 Y'),
('33 Years'),
('72 Years'),
('77 Years'),
('21 Years'),
('85 Years'),
('72YR'),
('29Y'),
('23 Years'),
('89 Y'),
('80 Years'),
('17 Years'),
('18 Years'),
('18 Years'),
('89 Years'),
('19 Years'),
('89 Y'),
('76 Years'),
('70 Y'),
('Unknown Ra'),
('20 Years'),
('46 Y'),
('25 Years'),
('Unknown'),
('74 Years'),
('Unknown'),
('65 Years'),
('49 Years'),
('95 Y'),
('21 Years'),
('93 Y'),
('34Y'),
('Unknown'),
('98 Y'),
('45 Years'),
('20 Years'),
('29 Years'),
('25 Years'),
('80 Years'),
('Unknown Ra'),
('90 Years'),
('20 Years'),
('96 Years'),
('89 Y'),
('49 Years'),
('57 Years'),
('Unknown'),
('78 Years'),
('50 Years'),
('88 Years'),
('29 Years'),
('57 Y'),
('89 Years'),
('48 Years'),
('96 Years'),
('80 Years'),
('27 Years'),
('25 Years'),
('21 Years'),
('62Y'),
(NULL)

-- Years
UPDATE #tmp SET 
	RandomText = CASE 
		WHEN ISNUMERIC(REPLACE(RandomText,'Years',''))=1 THEN REPLACE(RandomText,'Years','')
		ELSE randomText
	END

-- Y
UPDATE #tmp SET 
	RandomText = CASE 
		WHEN ISNUMERIC(REPLACE(RandomText,'Y',''))=1 THEN REPLACE(RandomText,'Y','')
		ELSE randomText
	END

-- what else is remaining?
SELECT * FROM #tmp WHERE ISNUMERIC(RandomText) = 0

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-08-22 : 19:41:21

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

rachew
Starting Member

4 Posts

Posted - 2013-08-23 : 03:19:03

Thanks very much for your help.
I'll try the suggestions and see which works best!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-23 : 14:25:03

Is it easy? Very clearly NO. But is it THAT difficult? Also NO.

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..



 --2 years, 2Yrs, 6, 6 months, 6mths, 1.5weeks, 3.5months old, 3 days, 15months etc etc etc.
DROP TABLE #ages
CREATE TABLE #ages (age_text varchar(100)); 
INSERT INTO #ages
SELECT '13 Years' UNION ALL
SELECT '72Y' UNION ALL
SELECT '57' UNION ALL
SELECT '78 Y' UNION ALL
SELECT '1.5W' UNION ALL
SELECT '2Yrs' UNION ALL
SELECT '3Yrs 6mos 3wks 10days' UNION ALL
SELECT '3Yrs6mos3wks10days' UNION ALL
SELECT '10days 3years' UNION ALL
SELECT '6 6months' UNION ALL
SELECT '6mths' UNION ALL
SELECT '1.5weeks' UNION ALl
SELECT '3.5months old' UNION ALL
SELECT '15months'
 
--y yr year year
--d day [dy?]
--w wk week
--m mth month month mnth

SELECT 
    age_text,
    LTRIM(RTRIM(SUBSTRING(age_text, day_literal - day_length, day_length))) AS age_days,
    LTRIM(RTRIM(SUBSTRING(age_text, week_literal - week_length, week_length))) AS age_weeks,
    LTRIM(RTRIM(SUBSTRING(age_text, month_literal - month_length, month_length))) AS age_months,
    LTRIM(RTRIM(SUBSTRING(age_text, year_literal - year_length, year_length))) AS age_years
    --, *
FROM #ages
CROSS APPLY (
    SELECT CHARINDEX('d', age_text) AS day_literal
) AS ca_day
CROSS APPLY (
    SELECT CHARINDEX('w', age_text) AS week_literal
) AS ca_week
CROSS APPLY (
    SELECT CHARINDEX('m', age_text) AS month_literal
) AS ca_month
CROSS APPLY (
    SELECT CASE WHEN day_literal = 0 OR CHARINDEX('y', age_text) < day_literal
        THEN CHARINDEX('y', age_text)
        ELSE CHARINDEX('y', age_text, day_literal + 3) END AS year_literal
) AS ca_year
CROSS APPLY (
    SELECT 
        CASE WHEN day_literal = 0 THEN 0 ELSE 
            PATINDEX('%[^0-9 .]%', REVERSE(LEFT(age_text, day_literal - 1)) + ',') - 1 END AS day_length,
        CASE WHEN week_literal = 0 THEN 0 ELSE 
            PATINDEX('%[^0-9 .]%', REVERSE(LEFT(age_text, week_literal - 1)) + ',') - 1 END AS week_length,
        CASE WHEN month_literal = 0 THEN 0 ELSE 
            PATINDEX('%[^0-9 .]%', REVERSE(LEFT(age_text, month_literal - 1)) + ',') - 1 END AS month_length,
        CASE WHEN year_literal = 0 THEN 0 ELSE 
            PATINDEX('%[^0-9 .]%', REVERSE(LEFT(age_text, year_literal - 1)) + ',') - 1 END AS year_length
) AS ca_lengths

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-23 : 14:55:14

quote:

Originally posted by ScottPletcher

Is it easy? Very clearly NO. But is it THAT difficult? Also NO.

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..



 --2 years, 2Yrs, 6, 6 months, 6mths, 1.5weeks, 3.5months old, 3 days, 15months etc etc etc.
DROP TABLE #ages
CREATE TABLE #ages (age_text varchar(100)); 
INSERT INTO #ages
SELECT '13 Years' UNION ALL
SELECT '72Y' UNION ALL
SELECT '57' UNION ALL
SELECT '78 Y' UNION ALL
SELECT '1.5W' UNION ALL
SELECT '2Yrs' UNION ALL
SELECT '3Yrs 6mos 3wks 10days' UNION ALL
SELECT '3Yrs6mos3wks10days' UNION ALL
SELECT '10days 3years' UNION ALL
SELECT '6 6months' UNION ALL
SELECT '6mths' UNION ALL
SELECT '1.5weeks' UNION ALl
SELECT '3.5months old' UNION ALL
SELECT '15months'
 
--y yr year year
--d day [dy?]
--w wk week
--m mth month month mnth

SELECT 
    age_text,
    LTRIM(RTRIM(SUBSTRING(age_text, day_literal - day_length, day_length))) AS age_days,
    LTRIM(RTRIM(SUBSTRING(age_text, week_literal - week_length, week_length))) AS age_weeks,
    LTRIM(RTRIM(SUBSTRING(age_text, month_literal - month_length, month_length))) AS age_months,
    LTRIM(RTRIM(SUBSTRING(age_text, year_literal - year_length, year_length))) AS age_years
    --, *
FROM #ages
CROSS APPLY (
    SELECT CHARINDEX('d', age_text) AS day_literal
) AS ca_day
CROSS APPLY (
    SELECT CHARINDEX('w', age_text) AS week_literal
) AS ca_week
CROSS APPLY (
    SELECT CHARINDEX('m', age_text) AS month_literal
) AS ca_month
CROSS APPLY (
    SELECT CASE WHEN day_literal = 0 OR CHARINDEX('y', age_text) < day_literal
        THEN CHARINDEX('y', age_text)
        ELSE CHARINDEX('y', age_text, day_literal + 3) END AS year_literal
) AS ca_year
CROSS APPLY (
    SELECT 
        CASE WHEN day_literal = 0 THEN 0 ELSE 
            PATINDEX('%[^0-9 .]%', REVERSE(LEFT(age_text, day_literal - 1)) + ',') - 1 END AS day_length,
        CASE WHEN week_literal = 0 THEN 0 ELSE 
            PATINDEX('%[^0-9 .]%', REVERSE(LEFT(age_text, week_literal - 1)) + ',') - 1 END AS week_length,
        CASE WHEN month_literal = 0 THEN 0 ELSE 
            PATINDEX('%[^0-9 .]%', REVERSE(LEFT(age_text, month_literal - 1)) + ',') - 1 END AS month_length,
        CASE WHEN year_literal = 0 THEN 0 ELSE 
            PATINDEX('%[^0-9 .]%', REVERSE(LEFT(age_text, year_literal - 1)) + ',') - 1 END AS year_length
) AS ca_lengths

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.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-23 : 16:12:05

Good point about number alone -- I'd actually meant to include that part but just forgot it.



SELECT 
    age_text,
    LTRIM(RTRIM(SUBSTRING(age_text, day_literal - day_length, day_length))) AS age_days,
    LTRIM(RTRIM(SUBSTRING(age_text, week_literal - week_length, week_length))) AS age_weeks,
    LTRIM(RTRIM(SUBSTRING(age_text, month_literal - month_length, month_length))) AS age_months,
    CASE WHEN year_literal = 0 AND month_literal = 0 AND week_literal = 0 AND day_literal = 0 
         THEN LTRIM(RTRIM(age_text))
         ELSE LTRIM(RTRIM(SUBSTRING(age_text, year_literal - year_length, year_length))) 
    END AS age_years
    --, *
FROM #ages
...

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-23 : 18:27:11

Scott, great solution!

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-28 : 07:03:51

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'

P.Kameswara rao

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources