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 12 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 EmployeeOfficer ZZ 3 Officer AA 4 Customer

Jignesh Trivedi · 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''12') ('ZZ''3''2') ('AA''4''3') insert into #Relationship values (1'Employee') (2'Officer') (3'Customer') (4'NoRelation') select nameId (select STUFF(( select '' + RelationName from #Relationship r inner join fn_split(RelationshipId'') f on f.splitdata = r.RelationshipId for xml path(''))11'')) 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.

Jignesh Trivedi · Answer

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.

Nagi Setty A · Answer

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