Here is another method, find the last character which is not space or numeric
😎
USE tempdb;
GO
;WITH SAMPLE_DATA(ID,Column1) AS
(SELECT ID,Column1 FROM
(VALUES
(1,'ABCD 123')
,(2,'XY 567')
,(3,'ghj 001')
,(4,'RUT')
,(5,'PUT')) AS X(ID,Column1)
)
,T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
SELECT
SD.ID
,SUBSTRING(SD.Column1,1,MAX(NM.N)) AS CLEAN_COL
FROM SAMPLE_DATA SD
CROSS APPLY
(SELECT TOP(LEN(SD.Column1)) (LEN(SD.Column1) + 1) - ROW_NUMBER()
OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7) AS NM(N)
WHERE ASCII(SUBSTRING(SD.Column1,NM.N,1)) NOT IN (32,48,49,50,51,52,53,54,55,56,57)
GROUP BY SD.ID,SD.Column1;
Results
ID CLEAN_COL
----------- ---------
1 ABCD
2 XY
3 ghj
4 RUT
5 PUT