SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE legal_entity
    ([first_name] varchar(7), [last_name] varchar(7), [address_1_zip] int)
;
    
INSERT INTO legal_entity
    ([first_name], [last_name], [address_1_zip])
VALUES
    ('Maria', 'Munoz', 33122),
    ('John', 'Glenn', 10003),
    ('Melissa', 'Johnson', 90210)
;
3 rows affected

CREATE TABLE cases
    ([style] varchar(22), [case_number] varchar(7), [create_date] varchar(23))
;
    
INSERT INTO cases
    ([style], [case_number], [create_date])
VALUES
    ('Munoz, Maria', 'SL12345', '2021-03-01 11:45:26.657'),
    ('Glenn, John', 'SL45678', '2021-03-02 12:10:14.552'),
    ('Johnson, Melissa (DNY)', 'SG42561', '2021-04-10 01:06:10.214')
;
3 rows affected

SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    legal_entity AS LE
INNER JOIN cases as C ON PATINDEX( CONCAT( LE.last_name,', ',LE.first_name,'%'),C.style) > 0
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date


first_name
last_name
address_1_zip
CombinedName


Maria
Munoz
33122
Munoz,Maria

John
Glenn
10003
Glenn,John

Melissa
Johnson
90210
Johnson,Melissa

first_name	last_name	address_1_zip	CombinedName
Maria	Munoz	33122	Munoz,Maria
John	Glenn	10003	Glenn,John
Melissa	Johnson	90210	Johnson,Melissa