Removing Leading Zeros from Varchar SQL Developer

Removing leading zeros from varchar sql developer

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);

How to remove leading 0 from string in Oracle SQL?

You can use the TRIM function:

SELECT TRIM(LEADING '0' FROM col)

Removing leading zeroes from a field in a SQL statement

select substring(ColumnName, patindex('%[^0]%',ColumnName), 10)

Trim leading zeroes if it is numeric and not trim zeroes if it is alphanumeric

Whenever possible (in this case it is), use standard string functions, such as SUBSTR, INSTR, TRANSLATE, etc. instead of regular expression functions. Regular expressions are much more powerful, but also much more time consuming (precisely for that reason), so they should be used only when really needed.

If the column name is str, then:

case when translate(str, 'z0123456789', 'z') is null
     then ltrim(str, '0')
     else str                      end

TRANSLATE will translate z to itself, all the digits to NULL, and all other characters to themselves. (Alas, the z, or SOME non-digit character, is needed.)

The input is all-digits if and only if the result of TRANSLATE is NULL.

Demo:

select str, case when translate(str, 'z0123456789', 'z') is null
                 then ltrim(str, '0')
                 else str
            end  as new_str
from
(
  select '000012345' as str from dual union all
  select '012321'    as str from dual union all
  select '00012JY12' as str from dual
);

STR       NEW_STR 
--------- ---------
000012345 12345    
012321    12321    
00012JY12 00012JY12

Oracle - Trim and Leading 0

You are not using substr() properly for getting the last 9th digits. Instead, you must pass -9 as an argument to substr(). You are getting the correct result with 0057889123995683 only because it only has two trailing zeros.

After a little bit playing with your code, this worked for me:

select TRIM(substr((TO_CHAR(TRIM(LEADING 0 FROM '0000000300043467'))),-9)) from dual

Change your code to:

THEN TRIM(substr((TO_CHAR(TRIM(LEADING 0 FROM s.sales_number))),-9))

However, this approach won't work if after removing the leading zeros, the number of digits is less than 9. To solve this, first remove the leading zeros and then invoke substr() if the length is greater 9 (using an IF statement) :

trimmed := TRIM(LEADING 0 FROM s.sales_number);

IF LENGTH(trimmed) > 9 THEN
 trimmed := substr(trimmed,-9);
END IF;

As @Ben indicated, TRIM() implicitly converts to a character, there's no need to convert to a character afterwards.

---

Related Topics