I am trying to convert both of the columns TOJ and TAR to a numeric number without MOS and YRS characters. For example, 18 YRS 0 MOS needs to be (18 * 12 0 = 216 months).

Table name is ld2.

More sample data (image)

I tried this:

select substring(TOJ,PATINDEX('%[0,9]%',TOJ), LEN(TOJ)) 

to get onlt the numbers in TOJ But did not work.

CodePudding user response：

cast(trim(replace(left(s, 2), 'Y', '')) as int) * 12  
cast(trim(replace(left(right(s, 5), 2), 'M', '')) as int)

Assumes the format is consistent and that year (and month) can only have two digits.

CodePudding user response：

select TOJ, CONVERT(DECIMAL(8,2), REPLACE(REPLACE(REPLACE(TOJ,'YRS','.'),'MOS',''),' ',''));

• First replace, replaces 'YRS' by '.'
• Second replace, replaces 'MOS' by ''
• Third replace, removes spaces (replaces ' ' by '')

If you started with '27YRS 7MOS', the replacing should end up with '27.7'.

If you convert '27.7' to a decimal, you should get the desired value.