Search code examples
oracletrim

Oracle Trim RegEx

I am trying to execute a join between two tables in Oracle where the column in one of the tables is a string and number in the other.

I need to perform some sort of trim function the string version because it is an 8 character field and will lead with 0s in cases where the number is less than 8 digits.

For example, 123 = '00000123'. How can I get the string '00000123' to equal '123' regardless of the number of leading 0s.

Thanks!!

Solution

  • Use to_number conversion function:

    SELECT to_number('00000123')
FROM dual;

| TO_NUMBER('00000123') |
|-----------------------|
|                   123 |

    Demo: http://sqlfiddle.com/#!4/1792d/18