CODEWITHSUNDEEP
sqloracle-database
Ibrahim
Ibrahim

Reputation: 34

How to convert a Varchar2 value into a number in Oracle SQL?

I am stuck in my database systems assignments and I would like some help here. I am implementing a database for a Netflix like platform and have a column called Duration which stores the duration of each movie/show in the following format:

enter image description here

I wanted to get the average duration of the shows/movies that are listed in the database.

Is there any method to convert "93 mins" to 93 as a number so that the average of the column could be calculated?

This is the schema for the table that has duration as a column:

enter image description here

Upvotes: 0

Views: 4470

Answers (1)

MT0
MT0

Reputation: 168730

You can find the SUBSTRing up until the first space character and then convert it using TO_NUMBER:

SELECT TO_NUMBER( SUBSTR( duration, 1, INSTR( duration, ' ' ) -1 ) )
FROM   details;

or, you can use a regular expression to get the substring of just the leading digits:

SELECT TO_NUMBER( REGEXP_SUBSTR( duration, '^\d+' ) )
FROM   details;

However, the best solution would be to stop using a VARCHAR2 data type and, instead, use a NUMBER column then you do not need to do the conversion and are storing numeric data in an appropriate data type rather than as a string. If you want to display the value with the units then perform that formatting when you need it.

Upvotes: 1

Related Questions