SQL Server Portal

Few days ago, I was working on a report and I came across an issue wherein I had a varchar [ID] column with alphanumeric data in it but I had to display only number from this column (no strings). I provided this solution long back and you can use it here as well. But given below is the better solution to accommodate this issue.

Let me create an example to demonstrate it.

USE tempdb
GO
CREATE TABLE tbl_sample
(
[ID] INT,
[String] varchar(50)
)
GO
INSERT INTO tbl_sample VALUES (1,'RCP0001')
INSERT INTO tbl_sample VALUES (2,'TEMP0231')
INSERT INTO tbl_sample VALUES (3,'PO0999')
INSERT INTO tbl_sample VALUES (4,'SO0341')
INSERT INTO tbl_sample VALUES (5,'SUP12')
INSERT INTO tbl_sample VALUES (6,'CUST76')
INSERT INTO tbl_sample VALUES (7,'241')
INSERT INTO tbl_sample VALUES (8,'0000')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

Solution :
You need to use PATINDEX to get the first non numeric character location and then you can use SUBSTRING to get the all numeric values.
Given below is the script.

USE tempdb
GO
SELECT
[ID]
, [String]
, SUBSTRING([String],PatIndex('%[0-9]%',[String])
,DATALENGTH([String])) AS [Numbers]
FROM tbl_sample
GO
--OUTPUT