Split string into array of strings using SQL?

Hi,

I have two tables within a SQL Server database; the first database has a column which is an identifier and another column which lists one of more email identifers for a second table, e.g.

ID      EmailID
--      ----------

1      100
2      101,102,103,104
3      105,106


The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g.

EmailID      EmailAddress
---------      ------------------

100      address100@email.com
101      address101@email.com
102      address102@email.com
103      address103@email.com
104      address104@email.com
105      address105@email.com
106      address106@email.com

I need to create a stored procedure or function that:

1. Selects an EmailID from the first table, based on a valid ID,

2. ‘Splits’ the EmailID field of the first table (using the comma separator) so that there is an array of EmailIDs and then,

3. Selects the relevant EmailAddress value from the second table, based on a valid EmailID stored in the array

Is there any way that this can be done directly within SQL Server using a stored procedure/function, without having to select and separate the EmailIDs using a separate development language and then making repeated calls to the database in order to select the relevant EmailAddress?

Thanks,

John