I’m attempting to create a new table but altering some of the data from my old table using the 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 becasue 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 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
new067 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4
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