Add leading zeros only to numeric field in CDS?

Due to the limited functionality in CDS syntax the only way I see is to nest 10 REPLACE functions to remove digits and compare the result with initial string. If it is initial, then you have only digits, so you can LPAD them with zeroes. If not - use the original value.

Here's my code:

@AbapCatalog.sqlViewName: 'Z_V_TEST'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Test'
define view Z_TEST as select from /bi0/mmaterial {
    cast(
      case replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(material,
        '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')
        when ''
        then lpad(material, 40, '0')
        else material
      end as abap.char(40)
    ) as MATERIAL_ALPHA,
    material
}

And the result is:

REPORT Z_TEST.

    select *
    from Z_V_TEST
    where material in ('LAMP', '000000000000454445')

      into table @data(lt_res)
    .
    cl_demo_output=>display( lt_res ).



MATERIAL_ALPHA                           | MATERIAL 
-----------------------------------------+-------------------
0000000000000000000000000000000000454445 | 000000000000454445 
LAMP                                     | LAMP 

Here is the 7.55 way of doing the thing, when the custom entities were introduced:

1. Create custom entity

   @EndUserText.label: 'Material List'
   @ObjectModel.query.implementedBy: 'ABAP:ZCL_MATERIAL_ZEROED'
   @Search.searchable: false
   define custom entity zcds_mat {
     key matnr  : abap.char( 40 );
         maktx  : abap.char( 40 );
   }
   

2. Create implementation class for it

    CLASS ZCL_MATERIAL_ZEROED DEFINITION
      PUBLIC
      FINAL
      CREATE PUBLIC .
   
      PUBLIC SECTION.
        INTERFACES if_rap_query_provider.
      PROTECTED SECTION.
      PRIVATE SECTION.
    ENDCLASS.
   
    CLASS ZCL_MATERIAL_ZEROED IMPLEMENTATION.
      METHOD if_rap_query_provider~select.
        DATA: it_result   TYPE  TABLE OF zcustomer_zero. "Internal table to be returned , easier to handle return if internal table is as same type of our data definition
        DATA: lv_param    TYPE string."Local variable to fetch and save parameter value
        TRY.
            TRY.
                IF io_request->is_data_requested( ). "Fetching incoming data
                  io_request->get_paging( ).
   
                  SELECT matnr maktx FROM makt INTO TABLE @it_result.
   
                  LOOP AT it_result ASSIGNING FIELD-SYMBOL(<fldsym>).
                    <fldsym>-matnr = | { <fldsym>-matnr ALPHA = OUT } |.
                  ENDLOOP.
   
                  io_response->set_total_number_of_records( lines( it_result ) ). "setting the total number of records which will be sent
                  io_response->set_data( it_result  ). "returning the data as internal table
                ENDIF.
              CATCH cx_a4c_rap_query_provider INTO DATA(lx_exc). "CX_A4C_RAP_QUERY_PROVIDER is now deprecated so use CX_RAP_QUERY_PROVIDER
   
            ENDTRY.
          CATCH cx_rfc_dest_provider_error INTO DATA(lx_dest).
        ENDTRY.
      ENDMETHOD.
    ENDCLASS.
   

Here we adding leading zeroes though the string templates

You cannot consume custom entity directly, but you can use it in OData service, just in case you have this intention.

how about to analyze result of "replace (lower(YourField), upper(YourField), '')"? Since replace is case sensitive then non empty result would indicate we have letters in the string.