SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH temp_mytable AS (

SELECT 'ABC:BKS:1023049101' AS acctno UNION ALL

SELECT 'ABC:UWR:19048234582' UNION ALL

SELECT 'ABC:UEW:1039481843'

)

SELECT SUBSTRING(acctno, PATINDEX('%:[0-9]%', acctno) + 1, LEN(acctno)) AS expected_output

FROM temp_mytable;