In my AS ABAP 7.50 system, I have a table where the material length is 18 and I need to expose it via CDS as if the material length was 40 like in S/4. The material IDs in the system can be numeric (with leading zeros) or alphanumeric. The material field needs to be casted to MATNR40, and if the ID is numeric, the leading zeros need to be added up to the 40 characters.
First, I tried `lpad. But of course, it also adds the leading zeros to the alphanumeric values:
lpad( cast(matnr as matnr40), 40, '0' ) as material_long,
Then I added a case
but I'm not able to make the condition work as I expect. As konstantin confirmed in the comments, it's not possible to use regex here as I attempted:
case when matnr like '%[^0-9.]%'
then lpad( cast(matnr as matnr40), 40, '0' )
else cast(matnr as matnr40)
end as material_long,
Is there a solution within the CDS itself to this problem?
Source table:
MATNR18 | Description |
---|---|
000000000000000142 | Numeric ID material |
MATERIAL_2 | Alphanumeric ID |
Expected result:
MATNR40 | Description |
---|---|
0000000000000000000000000000000000000142 | Numeric ID material |
MATERIAL_2 | Alphanumeric ID |
LIKE
comparison does not understand regular expressions. It's more wildcards but nothing fancy like you're trying to do. See the official sap documentation regex vs. like. If you'd like to use the regex in some ABAP code you could use^\d+$
to match only numerical values orNOT LIKE %_%
in SQL/CDS if all non-numerical values contained an underscore. – koks der drache