Get Revision Number From A Varchar Column

try below

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

Select Order_Number,
case when CHARINDEX('R',Order_Number) > 0 then SUBSTRING(Order_Number,CHARINDEX('R',Order_Number)+1,LEN(Order_Number)) else '0' end 
from #Orders

Thanks

Saravana Kumar C