Ron Jensen Ron Jensen - 23 days ago 7
SQL Question

sql trim some leading 0

I'm using an Oracle database, and need to trim some leading zeros, but I need to leave enough leading zeros to pad the field length to at least 4 characters. This is some sample data where 'myField' is the input and 'cooked' is what I want as the output:

myField cooked
0000000009 0009
0000123456 123456
00ABCE1234 ABCE1234


I used this to accomplish my goal, but was wondering if there is a better way

case
when length(trim(leading 0 from myfield)) >= 4 then trim(leading 0 from myfield)
else lpad(trim(leading 0 from myfield), 4 , 0)
end as cooked

Answer

There is! For example, using regexp_substr():

with
     test_data ( myfield ) as (
       select '0000000009' from dual union all
       select '0000123456' from dual union all
       select '00ABCE1234' from dual
     )
-- end of test data; solution (SQL query) begins below this line
select myfield, regexp_substr(myfield, '^0+(.{4,}$)', 1, 1, null, 1) as cooked
from   test_data
;

MYFIELD    COOKED   
---------- ----------
0000000009 0009      
0000123456 123456    
00ABCE1234 ABCE1234  

This assumes myfield is at least four characters long; if it isn't, and you need to pad with zeros when it isn't, you could simply replace myfield with '0000' || myfield in the function.

Comments