Yup. We run a system that uses this pattern for user-defined data fields. It works, but not without difficulty. It's very difficult to validate with business rules because you can't eliminate nulls and the "value" field is forced to be a character field. The system does have type checking enforced by the application, but multiple applications access the data.

> the "value" field is forced to be a character field

It doesn't have to be. We use this (under SQL Server) for our simple "custom attributes" mechanism (some irrelevant details omitted):

    CREATE TABLE private_data.ATTRIBUTE_VERSION (

        OBJECT_ID bigint,
        ATTRIBUTE_NAME nvarchar(255),
        ATTRIBUTE_GEN bigint,
        IS_TRASH bit NOT NULL,

        VALUE_BOOL bit,
        VALUE_INT bigint,
        VALUE_DECIMAL decimal(38, 9),
        VALUE_DATETIME datetime2,
        VALUE_STRING nvarchar(255),

        ATTRIBUTE_TYPE AS private_attributes.ATTRIBUTE_VALUE_TO_TYPE(
            VALUE_BOOL,
            VALUE_INT,
            VALUE_DECIMAL,
            VALUE_DATETIME,
            VALUE_STRING
        ),

        CONSTRAINT ATTRIBUTE_VERSION_PK PRIMARY KEY (OBJECT_ID, ATTRIBUTE_NAME, ATTRIBUTE_GEN),

        CONSTRAINT ATTRIBUTE_VERSION_FK1 FOREIGN KEY (OBJECT_ID, ATTRIBUTE_NAME) REFERENCES private_data.ATTRIBUTE,
        CONSTRAINT ATTRIBUTE_VERSION_FK2 FOREIGN KEY (ATTRIBUTE_GEN) REFERENCES private_data.GENERATION,

        CONSTRAINT ATTRIBUTE_VERSION_C1 CHECK ( -- At most one of the various value types can be non-NULL.
            private_attributes.ATTRIBUTE_VALUE_CHECK(
                VALUE_BOOL,
                VALUE_INT,
                VALUE_DECIMAL,
                VALUE_DATETIME,
                VALUE_STRING
            ) = 1
        )

    );

Since SQL Server encodes NULLs in a bitmap, having a bunch of NULLs for every non-NULL takes almost no extra space at all. But even on a "wasteful" database such as Oracle, this shouldn't add more than an extra byte per NULL.

> The system does have type checking enforced by the application, but multiple applications access the data.

Traditionally, this is solved by funneling all applications through a public API of stored procedures and views/functions which enforce the desired business logic, but I understand this approach has fallen out of favor in recent years (decades?).

That's really not an improvement. This complex enough that you're going to run into problems when multiple systems use the same database. I can imagine the fun that comes when another system wedges a field because it used the wrong type. That's the same problem we already have with a single text column. This is way more complex and still has the same problems.