Regex needed

Nagi A

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi11y ago
Accepted answer
hi,
try following code
Create table #Persons (Name varchar(50), ID int, RelationshipId varchar(10))
Create table #Relationship (RelationshipId int, RelationName varchar(50))
insert into #Persons values ('XX','1','1'),
('YY','2','1,2'),
('ZZ','3','2'),
('AA','4','3')
insert into #Relationship values (1,'Employee'),
(2,'Officer'),
(3,'Customer'),
(4,'NoRelation')
select name,Id,
(select STUFF((
select ',' + RelationName from #Relationship r
inner join fn_split(RelationshipId,',') f on f.splitdata = r.RelationshipId
for xml path('')),1,1,'')) as RelationName
from #Persons

you can get fn_split function defination in following link
http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/
hope this will help you.
+1
Jignesh Trivedi11y ago
Sorry friend, I did not have Oracle installed in my env. so could not help but you can download any MSSQL to Oracle query converter any try above query
Please refer
http://www.swissql.com/products/sql-translator/sql-converter.html
hope this will help you.

0
Nagi A11y ago
Hi Jignesh, Thank you very much for your quick and excact answer. I need to use it in oracle database. i am trying to applay in oracle database. Please can you help to get it in Oracle db, if possible. Thank you a lot. -Nagi Setty A
0