Regex needed

Question

Hi,

 

I have a tables named Persons (Name varchar, ID int, RelationshipId varchar) and Relationship (RelationshipId varchar, RelationName varchar).

 

Here is the Data in tables,

Person Table

Name   ID    RelationshipId

XX         1      1

YY         2      1,2

ZZ         3      2

AA         4      3

 

Relationship Table

RelationshipId       RelationName

1                              Employee

2                              Officer

3                              Customer

4                              NoRelation

 

 

RelationshipId values are dynamic in Person table. it may have multiple values in each cell. it will change dynamically, when it is upated  from application.

 

I want replace RelationshipId with RelationName from Relationship table in Person table. 

 

Please suggest query, regex to get as below,

 

Person Table

Name      ID              RelationshipName

XX           1                Employee

YY           2                Employee,Officer

ZZ           3                Officer

AA           4                Customer