Extraer dato de un texto SQL Server

Si, como en los datos que pones de ejemplo, el texto Basado en Pedidos de cliente 'XXX' puede estar en cualquier parte de tu campo, y no solo al final, y suponiendo que el número sea siempre de 5 dígitos, puedes hacerlo con esta SQL en una Vista de SQL Server:

Donde Campo1, Campo2... serán campos de tu tabla (si los quieres mostrar en el resultado), Texto sería el campo donde tienes esos textos, y código el nombre de la nueva columna resultante, y TJoseRomero el nombre de tu tabla

Incluso podrías mejorarlo así, por si hay registros que no tienen esa cadena:

Tengo que ponerte los códigos como imagen, que de otra forma no me deja.

Si siempre va a estar a la derecha y se va a componer de cinco dígitos puedes crear un campo, por ejemplo

Num=right([nombrede la columna];2) o  tambien Der([nombredela columna];5) Por ejemplo, construyo la consulta en Access pero la instrucción SQL es la misma

Con lo que al abrirla

Y en este caso la instrucción SQL es

SELECT Clientes.Concepto, Right([concepto],5) AS Num FROM Clientes;