oracle - Removing leading zeros from varchar sql developer

Question

How would I remove the leading zeros from a number which is in the form of varchar. I have tried the following:

Option 1:

insert into example_table (columnName) 
    (SELECT
        SUBSTR(columnName2, InStr('%[^0 ]%', columnName2 + ' '), 10)
        from columnName2);

With this, the error I get is

SQL Error: ORA-01722: invalid number
ORA-02063: preceding line from xxxx
01722. 00000 -  "invalid number"

Option 2:

insert into example_table (columnName) 
    (SELECT
        SUBSTR(columnName2, InStr('%[^0 ]%', columnName2 + ' '),  
        LEN(columnName2))
     from columnName2);

This time I get

Error at Command Line:23 Column:87
Error report:
SQL Error: ORA-00904: "LEN": invalid identifier

Option 3:

SUBSTRING
    (columnName2, PATINDEX('%[^0 ]%', columnName2 + ' '), 10));

Similar to above, I get

Error at Command Line:23 Column:41
Error report:
SQL Error: ORA-00904: "PATINDEX": invalid identifier
00904. 00000 -  "%s: invalid identifier"

EDIT

I think that the trim route might be my best option, however... I am uncertain how to use it in the case I have.

INSERT INTO temp_table
(columnNeedTrim, column2, column3, column4, column5) 
    SELECT * FROM
        (SELECT(
      SELECT TRIM(leading '0' from columnNeedTrim) FROM table),
            table.column2, 
            table2.column3,
                table.column4
                table.column5
        FROM
        table 
        INNER JOIN 
        table2 ON
            table1.columnNeedTrim=table2.columnNeedTrim) 
    WHERE NOT EXISTS (SELECT * FROM temp_table);

I now get an error because my trim function returns multiple row result.

Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"

I am not sure how to work a trim (or a cast) into the statement above. Any help on that? Thanks for any help!

Answer 1

Oracle has built-in TRIM functions for strings. Assuming you have a string like '00012345' and you want to keep it as a string, not convert it to an actual NUMBER, you can use the LTRIM function with the optional second setparameter specifying that you're triming zeros:

select ltrim('000012345', '0') from dual;

LTRIM
-----
12345

If you might also have leading spaces you can trim both in one go:

select ltrim(' 00012345', '0 ') from dual;

LTRIM
-----
12345

You could also convert to a number and back, but that seems like a lot of work unless you have other formatting that you want to strip out:

select to_char(to_number('000012345')) from dual;

---

Incidentally, the immediate reason you get the ORA-01722 from your first attempt is that you're using the numeric + operator instead of Oracle's string concentenation operator ||. It's doing an implicit conversion of your string to a number, which it seems you're trying to avoid, and the implicit conversion of the single space - whatever that is for - is causing the error. (Possibly some of your values are not, in fact, numbers at all - another example of why numbers should be stored in NUMBER fields; and if that is the case then converting (or casting) to a number and back would still get the ORA-01722). You'd get the same thing in the second attempt if you were using LENGTH instead of LEN. Neither would work anyway as INSTR doesn't recognise regular expressions. You could use REGEXP_INSTR instead, but you'd be better off with @schurik's REGEXP_REPLACE version if you wanted to go down that route.

---

I'm not sure I understand your question edit. It looks like your insert can be simplified to:

INSERT INTO temp_table (columnNeedTrim, column2, column3, column4, column5)
SELECT LTRIM(table1.columnNeedTrim, '0 '),
    table1.column2,
    table1.column3,
    table1.column4,
    table1.column5
FROM table1
INNER JOIN table2 ON table2.columnNeedTrim = table1.columnNeedTrim
WHERE NOT EXISTS (
    SELECT * FROM temp_table
    WHERE columnNeedTrim = LTRIM(t42.columnNeedTrim, '0 '));

(I don't understand why you're doing a subquery in your version, or why you're getting the trimmed value from another subquery.)

You could also use MERGE:

MERGE INTO temp_table tt
USING (
    SELECT LTRIM(t42.columnNeedTrim, '0 ') AS columnNeedTrim,
        t42.column2,
        t42.column3,
        t42.column4,
        t42.column5
    FROM t42 
    INNER JOIN t43 ON t43.columnNeedTrim=t42.columnNeedTrim
) sr
ON (sr.columnNeedTrim = tt.columnNeedTrim)
WHEN NOT MATCHED THEN
INSERT (tt.columnNeedTrim, tt.column2, tt.column3, tt.column4, tt.column5)
VALUES (sr.columnNeedTrim, sr.column2, sr.column3, sr.column4, sr.column5);