Sql Conditional Not Null Constraint

This is perfectly fine for CONSTRAINT CHECK. Just do this:

Requirement:

is it possible to create a constraint such that a column B can be null
as long column A contains lets say ‘NEW’ but if the contents of column
A changes to something else then column B is no longer allowed to be
null?

Note the phrase: column B can be null

Solution:

create table tbl
(
    A varchar(10) not null,
    B varchar(10),

    constraint uk_tbl check
    (
      A = 'NEW' -- B can be null or not null: no need to add AND here
      OR (A <> 'NEW' AND B IS NOT NULL)
    )
);

You can simplify it further:

create table tbl
(
    A varchar(10) not null,
    B varchar(10),

    constraint uk_tbl check
    (
      A = 'NEW' 
      OR B IS NOT NULL
    )
);

Requirement mutually incompatible to requirement above:

And to extend on that, it is then possible to make it so that column B
must be null or empty as long as column A says ‘NEW’?

Note the phrase: column B must be null

create table tbl
(
    A varchar(10) not null,
    B varchar(10),

    constraint uk_tbl check
    (
      (A = 'NEW' AND B IS NULL)
      OR A <> 'NEW'
    )
);

Could be simplified with this, simpler but might not be as readable as above though:

create table tbl
(
    A varchar(10) not null,
    B varchar(10),

    constraint uk_tbl check
    (
      A <> 'NEW'
      OR B IS NULL
    )
);