• 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;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001