How to write SQL query to get Distribution List members recursively?

I have a sample table [DL_Members] with Distribution Lists and their members

DL_Name            Member_Name
*Test - DL - 1	   Joe
*Test - DL - 1	   Mike
*Test - DL - 1	   Sarah
*Test - DL - 1	   *Test - DL - 2
*Test - DL - 2	   Brian
*Test - DL - 2	   Mary
*Test - DL - 2	   *Test - DL - 3
*Test - DL - 3	   Steve
*Test - DL - 3	   *Test - DL - 4
*Test - DL - 4	   Jane
*Test - DL - 4	   *Test - DL - 2

It is possible for a Distribution List to have people as members directly or other DL's.
Those other DL's can have their own people members or more DL's nested within them.

My challenge is how to write a SQL query to get all members of a DL including any nested DLs (all levels)?

For example if I simply do

SELECT
Member_Name
FROM DL_Members
WHERE DL_Name = '*Test - DL - 1'

The result will be:

>Joe<br>
>Mike<br>
>Sarah<br>
>*Test - DL - 2<br>
>*Test - DL - 3<br>

So my problem is that a simple query won't parse any nested DL's recursively
The result I want to get when running the query to get all members of *Test - DL - 1 is:

>Joe<br>
>Mike<br>
>Sarah<br>
>Brian<br>
>Mary<br>
>Steve<br>
>Jane<br>
>*Test - DL - 2<br>
>*Test - DL - 3<br>
>*Test - DL - 4<br>

I haven't been able to create a query that will get all members of a DL recursively.

Expanding on my comment about using a Recursive CTE here. Yours would look something like:

WITH reccte AS
(
/*Recursive Seed - This is the first selection that begins the iterations*/
SELECT DL_Name, Member_Name
FROM DL_Members
WHERE DL_Name = '*Test - DL - 1'
UNION ALL
/*Recursive Term - This SQL is run over and over again until the Join fails*/
SELECT DL_Members.DL_Name, DL_Members.Member_Name
FROM reccte
INNER JOIN DL_Members
ON reccte.Member_Name = DL_Members.DL_Name /*join member to DL*/
)
/*Select from the CTE*/
SELECT Member_Name FROM reccte
/*You can use the following to insure that infinite cycling doesn't occur*/
OPTION (MAXRECURSION 20); 

I haven't tested this, but it should be in the ballpark.

The following version keeps track of depth of recursion and kills the recursive term when it hits 20. If you want to go higher than 100 then you will also need that OPTION (MAXRECURSION N) where N is a higher number then your depth.

WITH reccte AS
(
/*Recursive Seed - This is the first selection that begins the iterations*/
SELECT DL_Name, Member_Name, 0 as depth
FROM DL_Members
WHERE DL_Name = '*Test - DL - 1'
UNION ALL
/*Recursive Term - This SQL is run over and over again until the Join fails*/
SELECT DL_Members.DL_Name, 
DL_Members.Member_Name,
reccte.depth + 1
FROM reccte
INNER JOIN DL_Members
ON reccte.Member_Name = DL_Members.DL_Name /*join member to DL*/
WHERE depth <= 20 --prevent cycling more than 20 iterations.
)
/*Select from the CTE*/
SELECT DISTINCT Member_Name FROM reccte;

Lastly, here is an example where we construct the member path for the searched DL. Every time we add a member, we also add that member to the path. We can then search the path for any member the current iteration is trying to add to see if it's already present. If so, it kills the iteration.

WITH reccte AS
(
/*Recursive Seed - This is the first selection that begins the iterations*/
SELECT DL_Name, Member_Name, 0 as depth, CAST(DL_NAME + '>' + Member_Name AS NVARCHAR(50000)) as path
FROM DL_Members
WHERE DL_Name = '*Test - DL - 1'
UNION ALL
/*Recursive Term - This SQL is run over and over again until the Join fails*/
SELECT DL_Members.DL_Name, 
DL_Members.Member_Name,
reccte.depth + 1,
reccte.path + '|' + DL_Members.Member_Name
FROM reccte
INNER JOIN DL_Members
ON reccte.Member_Name = DL_Members.DL_Name /*join member to DL*/
WHERE 
/*Prevent this from iterating more than 20 times*/
depth <= 20 
/*Also we can check to see if this member already exists in the path we collected*/
AND reccte.path NOT LIKE '%' + DL_Members + '%'
)
/*Select from the CTE*/
SELECT DISTINCT Member_Name FROM reccte;

I believe this is what you are looking for:

select  Member_Name 
from DL_Members
where RIGHT ( DL_Name , 1 ) >= RIGHT ( '*Test - DL - 1' , 1 ) ;

Here is a DEMO for you.