Changing to compatibility level 150 makes SQL Server consume all available memory
You're encountering a known bug with scalar UDF inlining.  You can disable inlining using one of the methods here (or by using a lower compat level, as you've discovered yourself):

Disabling Scalar UDF Inlining without changing the compatibility level

Or install CU2 for the permanent fix.




  ...even running a simple scalar function (which internally uses several Substring and cast calls - no table or view selects) is enough to make it consume all the available memory...


Reading this part of your issue reminded me of this really interesting blog post from sqL_handLe:

SQL Server 2019 Scalar UDF inlining - OOM in some cases

He demonstrates a scalar UDF that doesn't do any data access, but uses up all the memory on the box (even one with almost a TB of RAM):


  Not just crashing itself.  Crashing any other query on the instance that was trying to allocate memory (eg stealing against a query memory grant).


This should be fixed in SQL Server 2019 CU2, it looks like this is the KB for it: 

FIX: Scalar UDF Inlining issues in SQL Server 2019  


  This cumulative update includes several fixes across the following areas for scenarios where a query that uses Scalar UDF Inlining may return an error or unexpected results:  
  
  
  ...
  Out of memory conditions and memory leaks due to very large scalar UDFs 
  ...
  


If you're unable to install CU2 for some reason, you can use the newer compatibility level as long as you disable scalar UDF inling at the database level like this:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;


Or at the query level, as described in the docs article I linked to earlier.