There's many ways to tackle this, here's a few:Sample data
CREATE TABLE #TEMP(ID Varchar(200));
INSERT #TEMP
VALUES ('ABC205916_DAN'),('ABC243296'),('ABC222249_DAN25'),('IN217465_v99'),('ABC#243296');
GO
If it's always 6 numbers you're looking for you could do this:
SELECT
ID,
ID_NEW =
SUBSTRING
(
ID,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9]%', ID),
6
)
FROM #TEMP;
If it's always the first group of numbers you can do this
WITH number_start AS
(
SELECT
ID,
start = SUBSTRING(ID, PATINDEX('%[0-9]%', ID), 200)
FROM #TEMP
)
SELECT
ID,
ID_NEW =
SUBSTRING
(
start, 1,
ISNULL(NULLIF(PATINDEX('%[^0-9]%', start)-1,-1), 200)
)
FROM number_start;
This may also work; using PatternSplitCM:
SELECT
ID,
ID_NEW = item
FROM #TEMP
CROSS APPLY dbo.PatternSplitCM(ID, '%[0-9]%')
WHERE ItemNumber = 2;
-- Itzik Ben-Gan 2001