I am trying to create age band from a varchar column with both numeric and categorical. the column contains age values
0-12
13
11
23
30
25
32
31
34
35
42
43
39
47
50
55
61
62
over 64
favorites
I tried this
CASE
WHEN Age='0-12' THEN '0-12'
when Age BETWEEN 15 AND 30 THEN '15-30'
when Age BETWEEN 31 AND 63 THEN '31-63'
when Age= 'over 64' then 'over 64'
when Age='favorites'then 'favorites'
END AS 'Age group',
or below
CASE
when Age BETWEEN 15 AND 30 THEN '15-30'
when Age BETWEEN 31 AND 63 THEN '31-63'
when Age= 'over 64' then 'over 64'
else Age
END AS 'Age group'
I was expecting
0-12
15-30
31-63
over 64
favorites
I keep getting following error
Conversion failed when converting the nvarchar value '0-12' to data type int.
What am I doing wrong and whats the correct way to write this please? I am also happy to try this in python (if someone has a solution) thanks in advance for the help
You need to use TRY_CONVERT because you are comparing Age to an int range, but not all your Age values can be implicitly converted to an int. TRY_CONVERT doesn't give an error when it is unable to convert a value.
You also don't need to explicitly test for the other values since you want to use them unchanged.
SELECT *,
CASE
WHEN TRY_CONVERT(int, Age) BETWEEN 15 AND 30 THEN '15-30'
WHEN TRY_CONVERT(int, Age) BETWEEN 31 AND 63 THEN '31-63'
ELSE Age
END AS 'Age group'
FROM Test;