By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t(tokens varchar(100));
insert into t values
('abc 1XXXX xyz'),
('abc xyz 2XXXXXXXX'),
('3XX abc xyz'),
('0XXXXX abc 123 qwerty'),
('XX XXX 35abc xyz'),
('XX 9XXX xyz'),
('X3X 7bc xyz')
7 rows affected
select *,
Left(Stuff(tokens, 1, PatIndex('%[^A-z][0-9]%', tokens), ''),
IsNull(NullIf(CharIndex(' ', Stuff(tokens, 1, PatIndex('%[^A-z][0-9]%',tokens), '')),0), Len(tokens)) ) as Result
from t
| tokens | Result |
|---|---|
| abc 1XXXX xyz | 1XXXX |
| abc xyz 2XXXXXXXX | 2XXXXXXXX |
| 3XX abc xyz | 3XX |
| 0XXXXX abc 123 qwerty | 123 |
| XX XXX 35abc xyz | 35abc |
| XX 9XXX xyz | 9XXX |
| X3X 7bc xyz | 7bc |