CODEWITHSUNDEEP
sqloracle-database
user3561134
user3561134

Reputation: 1

Multiple decimal string conversion to number

Working on 10g.

I have a regexp that returns the records numeric values, but in some cases returns multiple decimal points such as 68.70125195853.50

What I need is a way to only return the two decimal places after the first decimal, like 68.70.

I have tried built-in functions like to_char, to_number, and round.

I would very much appreciate your help.

Upvotes: 0

Views: 2891

Answers (4)

Alessandro Rossi
Alessandro Rossi

Reputation: 2450

You could probably use the following regexp inside a to_number

to_number(
    regexp_substr(
        '68.70125195853.50',
        '^[0-9]+(\.[0-9]{1,2})?'
    )
)

In words: begins with a series of numeric chars, eventually followed by a dot then from 1 to 2 more numeric chars.

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26333

This should work if you need only the first two decimal places. Note that without sample data the answer could be off; if so please provide examples of some of the values you're having trouble with:

SELECT CAST(REGEXP_SUBSTR(myVal, '(\d+\.\d{0,2})|(\d+)') AS NUMBER(20, 2))
FROM myTable
WHERE REGEXP_LIKE(myVal, '(\d+\.\d{0,2})|(\d+)')

The regex should work with no decimal place, as well as with one or two decimal places. Note that anything after the second decimal place will be truncated, so no rounding will occur. If you want rounding, change the \d{0,2} to \d+, and the CAST will take care of rounding.

The WHERE clause ignores columns that don't contain a number, which prevents casting errors.

Addendum: here's an explanation of the regex. It looks for one of two patterns.

The first pattern is (\d+\.\d{0,2}). This catches numbers with decimal places.

  • ( ==> delimits the first pattern, defining the beginning of the first pattern
  • \d+ ==> match one or more digits (\d means "any digit")
  • \. ==> match a period (the period is a "special" character so to match it literally you need to escape it with the slash (\)
  • \d{0,2} ==> match zero, one or two digits
  • ) ==> delimits the first pattern, defining the end of the first pattern

The second pattern is (\d+). It catches numbers without decimal places.

  • ( ==> defines the beginning of the second pattern
  • \d+ ==> match one or more digits
  • ) ==> defines the end of the second pattern

Finally, the two patterns are joined with the OR operator (|) so you'll get a match if either pattern matches.

  • (first-pattern)|(second-pattern)

The order of the patterns is important here, because a "number with decimals" also matches the "number without decimals" pattern; that's why the "number with decimals" pattern is first.

Upvotes: 1

Curt
Curt

Reputation: 5722

Try: 

 CAST(value AS Decimal(15,2))

If you're just concerned with the "look" of the numbers, you can use:

SELECT TO_CHAR(num, '999,999,990.00')
  FROM MyTable

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Here is one method:

select (case when val like '%.%'
             then cast(substr(t.val, 1, instr(val, '.')+2) as float)
             else cast(val as float)
        end)
from (select '68.70125195853.50' as val from dual) t;

The case statement is checking for at least one decimal point.

Upvotes: 0

Related Questions