SELECT X AS A, N AS B
INTO TXNS
FROM (VALUES(1,'1a'),(2,'fwsa38#'), (3, '01a'),(4, '01b'),(5, '1b'),(6, '01c'),( 7, '1c') ,( 8, '10c')
	)a(X,N);

SELECT *
FROM TXNS

;WITH Tally(N) AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
                  FROM(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))a(N))
SELECT A,B,stripped 
FROM TXNS b
CROSS APPLY (SELECT 
				(SELECT CASE 
					WHEN PATINDEX('[0-9]',SUBSTRING(B,N,1)) = 1 
					THEN SUBSTRING(B,N,1)
                    ELSE '' END
                  FROM Tally
                  WHERE N <= LEN(B)
                  FOR XML PATH(''), TYPE
                  ).value('.', 'VARCHAR(10)') 
            ) c(stripped)

DROP TABLE TXNS

quote:

---

Originally posted by dave1816

Thanks Chandu!
first solution worked great! just got to find out why it won't work in crystal now very strange

---