CODEWITHSUNDEEP
oracle-databasetypestype-conversion
CodeMoto
CodeMoto

Reputation: 353

Determining the data type of a string value in Oracle

Ok...this is what I've got. There's an Oracle database table that has a column which is a VARCHAR data type. This column, call it 'value', contains strings that represent different values for different things. So some records have a number in this column, others have scientific notation, others dates, other actual characters etc. but they'll all are stored as strings in this 'value' column.

I need to write a view that can query this table and determine what data type is represented by the string in the 'value' column. I know I can do PL/SQL to do this, but I don't have access to create a stored procedure and this needs to be in a view - for other reasons.

Upvotes: 0

Views: 430

Answers (1)

codingbiz
codingbiz

Reputation: 26386

I think you need to have an extra column describing the value stored in the Value column

 Value       VARCHAR2,
 ValueType   VARCHAR2  -- e.g. Age, Name, Money

In your view you then use a CASE statement to determine

 CASE 
    WHEN ValueType = 'Age' THEN 'integer'
    WHEN ValueType = 'Name' THEN 'string'
    WHEN ValueType = 'Money' THEN 'decimal'
 END As ColumnValueType

You can also use Regular expression

Upvotes: 1

Related Questions