zebiri djallil zebiri djallil - 6 months ago 11
SQL Question

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:


  • SUBSTR('00008800000006',0,7) = '0000880' => Serno = '880'

  • SUBSTR('00008800000006',8,14) = '0000006' => lot = '6'



enter image description here

Answer

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