INSERT INTO SELECT subquery that uses a udf so that it returns 1 value

I'm attempting to create a new table but altering some of the data from my old table using a INSERT INTO SELECT statement. I'm trying to take a field with numbers and letters and pull out the numbers and multiply those number based on whether the number represented days, hours, or minutes and insert the result into my new table.

I found a user defined function to a get the number, www.geeksforgeeks.org/sql-query-to-get-only-numbers-from-a-string/. While the function works in a SELECT statement, when I use it as an expression in a subquery it fails because it returns more than 1 value. How can I rewrite the subquery so that it returns only 1 value? Or is there a better way to accomplish what I want?

The column I'm using the function on is called 'duration'. The column is defined as VARCHAR with data such as 'Concentration, up to 1 hour' and '24 hours'. I've tried using EXEC instead of SELECT but I get multiple syntax errors.

    CASE
        WHEN s.duration IN ('Instantaneous')
            THEN '0'
  
        WHEN s.duration LIKE '%round%'
            THEN '1'
  
        WHEN s.duration LIKE '%minute%'
            THEN (SELECT dbo.getNumericValue(duration) * 10 
                  FROM Spell.Spells 
                  WHERE duration LIKE '%minute%')                                                                                                                                  
        
        WHEN s.duration LIKE '%hour%'
            THEN  (SELECT dbo.getNumericValue(duration) * 600 
                   FROM Spell.Spells  
                   WHERE duration LIKE '%hour%')

        WHEN s.duration LIKE '%day%'
            THEN (SELECT dbo.getNumericValue(duration) * 14400 
                  FROM Spell.Spells 
                  WHERE duration LIKE '%day%')

        ELSE s.duration
    END AS duration,
    ritual,
    verbal,
    somatic,
    material,
    material_component,
    material_cost,
    material_consumed,
    description,
    source
FROM 
    Spell.Spells AS s

Something like this:

CASE
    WHEN s.duration IN ('Instantaneous')
        THEN 0

    WHEN s.duration LIKE '%round%'
        THEN 1

    WHEN s.duration LIKE '%minute%'
        THEN dbo.getNumericValue(duration) * 10                                                                                                            
        
    WHEN s.duration LIKE '%hour%'
        THEN  dbo.getNumericValue(duration) * 600 

    WHEN s.duration LIKE '%day%'
        THEN dbo.getNumericValue(duration) * 14400 

    ELSE cast(s.duration as int)
END AS duration,
        ritual,
        verbal,
        somatic,
        material,
        material_component,
        material_cost,
        material_consumed,
        description,
        source
    FROM Spell.Spells AS s