SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Age convertor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rachew
Starting Member

United Kingdom
4 Posts

Posted - 08/22/2013 :  04:20:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/22/2013 :  04:39:44  Show Profile  Reply with Quote
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
Go to Top of Page

rachew
Starting Member

United Kingdom
4 Posts

Posted - 08/22/2013 :  04:51:59  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/22/2013 :  07:24:04  Show Profile  Reply with Quote
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
Go to Top of Page

rachew
Starting Member

United Kingdom
4 Posts

Posted - 08/22/2013 :  08:06:39  Show Profile  Reply with Quote
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'
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 08/22/2013 :  08:24:41  Show Profile  Reply with Quote
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
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1770 Posts

Posted - 08/22/2013 :  19:41:21  Show Profile  Reply with Quote
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
Go to Top of Page

rachew
Starting Member

United Kingdom
4 Posts

Posted - 08/23/2013 :  03:19:03  Show Profile  Reply with Quote
Thanks very much for your help.
I'll try the suggestions and see which works best!
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
421 Posts

Posted - 08/23/2013 :  14:25:03  Show Profile  Reply with Quote
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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 08/23/2013 :  14:55:14  Show Profile  Reply with Quote
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.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
421 Posts

Posted - 08/23/2013 :  16:12:05  Show Profile  Reply with Quote
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
...

Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
342 Posts

Posted - 08/23/2013 :  18:27:11  Show Profile  Reply with Quote
Scott, great solution!
Go to Top of Page

kameswararao polireddy
Starting Member

India
19 Posts

Posted - 08/28/2013 :  07:03:51  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000