Extract four digits from a column by regexp
If the four digits you need to extract are either exactly at the end of the string or are followed only by spaces, it would probably be easiest to extract them using the RIGHT() function after removing the trailing spaces using the RTRIM() function:

SELECT
  RIGHT(RTRIM(YourColumnName), 4) AS SomeCode
FROM
  YourTableName
WHERE
  ...
;