CODEWITHSUNDEEP
sqloracle-databasesubstring
zebiri djallil
zebiri djallil

Reputation: 334

Remove additional zeroes from varchar

I want to remove all zeros 0 from the beginning of a string:

SQL:

SELECT SUBSTR('00008800000006',0,7)  AS serno ,
       SUBSTR('00008800000006',8,14) AS lot
FROM dual ;

Expected result:

enter image description here

Upvotes: 2

Views: 42

Answers (1)

vercelli
vercelli

Reputation: 4757

In case you are using Oracle, you may try with this:

select
 REGEXP_REPLACE(SUBSTR('00008800000006',0,7), '^0+(.*$)', '\1') as Serno,
 REGEXP_REPLACE(SUBSTR('00008800000006',8,14), '^0+(.*$)', '\1') as lot
 from dual;

OUPUT

SERNO    LOT

880      6

You may also use LTRIM

select
 ltrim(SUBSTR('00008800000006',0,7), '0') as Serno,
 ltrim(SUBSTR('00008800000006',8,14), '0') as lot
  from dual

OUPUT

SERNO    LOT

880      6

Upvotes: 3

Related Questions