CODEWITHSUNDEEP
sql-serverregexstringoracle-databasedate
SQLBI
SQLBI

Reputation: 3

How to find integer values within a String in SQL Server

I am trying to calculate tenure in years based on data in this format 2 year(s), 11 month(s), 5 day(s). Below is the logic in oracle. I am trying to implement similar solution in SQL Server. Can you please help?

In this example 2 year(s), 11 month(s), 5 day(s) is converted to 2.93 years.

round( 
    (regexp_substr(tenure_in_current_job,'[^ ]+')
    + REGEXP_REPLACE(tenure_in_current_job,'(.*r\(s\), )(\d+)(.*)','\2')/12
    + REGEXP_REPLACE(tenure_in_current_job,'(.*h\(s\), )(\d+)(.*)','\2')/365),
2 )

Upvotes: 0

Views: 46

Answers (1)

Bill Jetzer
Bill Jetzer

Reputation: 1115

select sum(num.val * 1.0 / case when trm.val like '%year%' then 1 when trm.val like '%month%' then 12 else 365 end)
from string_split('2 year(s), 11 month(s), 5 day(s)',',')
     cross apply (select ltrim("value")) trm(val)
     cross apply (select cast(left(trm.val,charindex(' ',trm.val)-1) as int)) num(val)

--------------
2.930365296802
  1. Split the string on commas
  2. Remove leading spaces on each of the 3 resulting rows
  3. Remove everything from the first space onward, and cast as int
  4. Divide the resulting int by 1, 12, or 365 as appropriate

Caveat: string_split was introduced in SQL Server 2016. If you're on SQL 2012 or older, this solution won't work.

Upvotes: 2

Related Questions