How to trim/substr from varchar when decimal place found in oracle?

Question

How to trim after decimal(.) from varchar2 in oracle?
Example 1: '2999.89' should be return '2999'
Example 1: 'V59.00' should be return 'V59'

Answer 1

You can use instr to find the index of . and then substr to return the string up to that position:

SELECT SUBSTR (col, 1, INSTR(col, '.') - 1)
FROM   mytable

Answer 2

You could use SUBSTR and INSTR.

For example,

SQL> WITH DATA(str) AS(
  2  SELECT '2999.89' FROM dual UNION ALL
  3  SELECT 'V59.00' FROM dual
  4  )
  5  SELECT str, SUBSTR(str, 1, instr(str, '.', 1, 1)-1) new_str FROM DATA;

STR     NEW_STR
------- -------
2999.89 2999
V59.00  V59

SQL>

Answer 3

WITH DATA(str) AS(
    SELECT '2999.89' FROM dual UNION ALL
    SELECT 'V59.00' FROM dual
)
SELECT str, REGEXP_SUBSTR(str,'^([^.]*)',1) new_str FROM DATA;