Get Revision Number From A Varchar Column

Try the below:

select 'OU123456' Order_Number into #Orders union all
select 'RQ123456R1' union all
select 'OU987987987R11' 

Select 
        Order_Number,IsNull(nullif(SUBSTRING(REVERSE(Order_Number),0,CHARINDEX('R',REVERSE(Order_Number))),''),0)
FROM #Orders

Drop table #Orders