Extract last 1-3 numbers if they are numbers and put them into their own column

Question

I have a temp table (#testTempTbl1) that i created in SQL in SSMS. i am trying to get a new column to have only the last 1 to 3 characters at the end of the string if they are numbers extracted from an existing column in the table and put them into their own column.

not sure if doing a if statement would be best or another method

The column i want to pull from is Description and it has values like ones below:

part3_sec4_line1_F3_DIR_DESC_1


part3_sec4_line1_F3_DIR_PAS_13

part3_sec4_line1_F3_DIR_FORGEN_12

The new column i am trying to just get the numbers at the end, some are 1 to 3 numbers at the end after the last '_':

1

12

13

12

Thank you in advance.

I am using SSMS.

Answer 1

    CREATE TABLE #testTempTbl1 (
        ID INT PRIMARY KEY, Description NVARCHAR(100)
    );
    
    -- Insert
    INSERT INTO #testTempTbl1 (ID, Description)
    VALUES
        (1, 'part3_sec4_line1_F3_DIR_DESC_1'),(2, 'part3_sec4_line1_F3_DIR_PAS_13'),
        (3, 'part3_sec4_line1_F3_DIR_FORGEN_12'), (4, 'part3_sec4_line1_F3_DIR_PAS_1301');
    
    -- Add a new column 
    ALTER TABLE #testTempTbl1 ADD ExtractedNumbers NVARCHAR(5);
    
    -- Update the new column
    UPDATE #testTempTbl1 SET ExtractedNumbers = REVERSE(SUBSTRING(REVERSE(Description), 1, PATINDEX('%[^0-9]%', REVERSE(Description)) - 1));
    
    --select
SELECT * FROM #testTempTbl1;