Sql,how to get these results?

Question

Here is the table structure and output i need to need please provided some suggestion.

| requestid | requeststatus |         note             | lasted updated date |
|     2123  |     open      | copy from requestid 1234 | 2018/8/19           |
|     2124  |    follow up  | copy from requestid 3456 | 2018/8/20           |

how to write a function to get result of requestid which is in the note. for example i need to get 1234(which is request id from there user information is copy) as output.

Answer 1

with cte as (
select distinct  requestid, reverse(LEFT(REVERSE(Note),CHARINDEX(' ',REVERSE(Note)))) as Cloneformid ,note,CreatedDate,ROW_NUMBER()  OVER(PARTITION BY RequestID ORDER by CreatedDate desc) as rn 
from table 
where   Note like 'cloned from request Id %' 
)
select RequestID, cast(Cloneformid  as int) as Cloneformid
from cte
where   rn =1 and  isnumeric(Cloneformid) = 1