SQL: Select rows from Comma Separated IDs

It's not a very common scenario when IDs are present as Comma Separated data and you have to select all rows against those IDs.

For example, if Addresses is a table and its primary key is u_address_id
There is another table OrganizationObject, which would store details of Organizations. The organizations' addresses are stored in Addresses table.

Ideally the Addresses table should contain a column to store primary key of OrganizationObject. Unfortunately, if you don't have that column, the data is stored in reverse order, where all the addresses's IDs for a particular organization are stored in CSV format in OrganizationObject row itself and you don't even have the privilege of changing the structure, here's a solution.

select ad.u_country_name as Country, aid.u_Name as ProviderName, aid.u_organization_type as ProviderType  from (
SELECT 
 F1.u_Name,
F1.u_organization_type,
O.u_address_id
FROM
(
SELECT *,
cast(''+replace(F.u_addresses,';','')+'' as XML) as xmlfilter from OrganizationObject F
where F.u_organization_type in ('2', '3')
)F1
CROSS APPLY
( 
 SELECT AData.A.value('.','varchar(50)') as u_address_id 
 FROM f1.xmlfilter.nodes('X') as AData(A)) O) aid --Address IDs
join
      Addresses ad
on 
      aid.u_address_id = ad.u_address_id  
where 
      ISNULL(NULLIF(u_country_name, ''), NULL) is not null 
      and ad.u_st_address_type = 2
order by u_country_name