CODEWITHSUNDEEP
sqloracle-databaseselectinsert
JLearner
JLearner

Reputation: 1311

Conversion of varchar to number

I have a question that bothers me. How can i convert a varchar to number when inside the varchar value consists of alphabets.

For my varchar price column values:

14dollars10cents
15dollars20cents

By converting it to varchar to number price column, the values should be:

1410
1520

I know that if the varchar does not consists any alphabets, it can auto convert by"

SELECT CONVERT(INT, PRICE) FROM Table

Is there any way to get rid of the alphabets in the middle as I would like to do mathematical function on it.

Updated attempt of putting fixed point number in:

SELECT CAST (Replace(REPLACE(PRICE, 'dollars', '.'),'cents','') AS Number(4,2))) 
FROM TEST;

Thanks

Upvotes: 5

Views: 15226

Answers (4)

adam2510
adam2510

Reputation: 563

SELECT CAST(REPLACE(YourVarcharCol, 'dollars', '') AS INT) FROM Table

The issue with this is it will break if the varchar still contains alpha-numeric characters.

Upvotes: 2

Kevin Burton
Kevin Burton

Reputation: 11936

How about using translate to strip out the unwanted characters.

SELECT TO_NUMBER(TRANSLATE('14dollars10cents','1234567890dolarscents','1234567890')) FROM DUAL

Upvotes: 0

Ollie
Ollie

Reputation: 17538

You could just use REGEXP_REPLACE to remove all non digit characters:

SELECT REGEXP_REPLACE(price, '[^[:digit:]]')
  FROM table;

To then convert this to a number:

SELECT TO_NUMBER(REGEXP_REPLACE(price, '[^[:digit:]]'))
  FROM table;

If you want to add the point in then you can do that with REGEXP_REPLACE too:

SELECT TO_NUMBER(REGEXP_REPLACE(val, '^([0-9]*)([^[:digit:]]*)([0-9]*)(.*)$', '\1.\3'))
  FROM table;

Voila...

Upvotes: 4

Sandip
Sandip

Reputation: 3761

No I don't think there is direct way. you can do string parsing to get your integer value.

Upvotes: -2

Related Questions