CODEWITHSUNDEEP
sqloracle-databasesubquery
ad2387
ad2387

Reputation: 571

Multiply two values together from a single database column

I have some data as follows:

Name             Processors
dab1             8x10 - 1.06 GHz
bac3             2x6 - 2.4 GHz
abc1             4x8 - 2 GHz
cab2             2x4 - 1.2 GHz

From the 'Processors' column, I need to be able to multiply the first number and the second number together (ex: 8*10 = 80, 2*6 = 12, etc) to check a condition.

Example query:

SELECT * FROM HOST_TABLE WHERE COMPUTED_PROCESSOR_VALUE > 16

How can I calculate this value? There will always be an 'x' between the numbers but the numbers could be single or double digit.

Thanks!

Upvotes: 0

Views: 76

Answers (2)

Linger
Linger

Reputation: 15058

SQL Fiddle

SELECT m.Name, m.Processors FROM
(
  SELECT Name, Processors, SUBSTR(Processors, 1, INSTR(Processors, 'x', 1, 1)-1) AS FirstValue,
  SUBSTR(Processors, INSTR(Processors, 'x', 1, 1) + 1, INSTR(Processors, ' ', 1, 1) - INSTR(Processors, 'x', 1, 1)) AS SecondValue
  FROM MyTable
) m 
WHERE m.FirstValue * m.SecondValue > 16

Upvotes: 1

givanse
givanse

Reputation: 14953

Use regular expressions, your query would look similar to this:

SELECT TO_NUMBER(REGEXP_SUBSTR(
           processors, '^[:digit:]{1,2}')) * 
       TO_NUMBER(REGEXP_SUBSTR(
           processors, '[:digit:]{1,2})', INSTR(processors, 'x'))) 
       AS computed_processor_value 
FROM host_table 
WHERE computed_processor_value > 16;

I can't test it right now, but it should get you started.

References for Oracle 10.1g (adjust for your version if necessary):

REGEXP_SUBSTR

Multilingual Regular Expression Syntax

Upvotes: 0

Related Questions