How to extract the integer part age from a varchar age column-CodePudding

In my SQL Server table, I have an age column which has values like 62-year old, 35-year old, 2 -year old and ABCD.

I want to create a new age column where if AGE is 65 and over. Group them to 65yrs old and older AND if AGE is under 65 then 'Under 65yrs old'

I tried using substring function like this

SUBSTRING(TRIM(' 62-year old  '), 1, 2)

The problem is that there are some ages in 1 digit like 8-year old where it fails with an error

Conversion failed when converting the varchar value '2-' to data type int

This is my CASE condition

 SELECT
     (CASE 
          WHEN SUBSTRING(TRIM('- ' from age), 1, 2) >= 65  
              THEN '65 Years Old and older' 
              ELSE 'Under 65 Years Old'
      END) age_group 
FROM
    table

Can someone please help me?

CodePudding user response：

Assuming a current version of SQL Server (you haven't tagged any specific version), a quick way to just extract the integers from a text string would be to use translate, such as:

declare @MyBadData varchar(50) = 'Under 65 years old';
select Trim(Translate(@MyBadData, ' ABCDEFGHIJKLMNOPQRSTUVWXYZ-', Space(28)));

CodePudding user response：

If all numbers are replaced by '' then the result is a group of characters and if these characters are replaced by '' then the result is a number. For multiple numbers, split the string by a nonnumerical character, fiddle :

select *
from(values(' 62-year old  ')) as t(age)
cross apply (values(replace(translate(t.age, '234567890', '111111111'), '1', ''))) as c(c)
cross apply (values(replace(translate(t.age, c.c, replicate('a', len(c.c '.')-1)), 'a', ''))) as n(n);

select *
from(values(' 62-year old and-70 year old')) as t(age)
cross apply (values(replace(translate(t.age, '234567890', '111111111'), '1', ''))) as c(c)
cross apply (
select value
from string_split(translate(t.age, c.c, replicate('a', len(c.c '.')-1)), 'a') as s
where s.value <> ''
) as n;