Conditional Join based on string parse

[justTryingToCode]

I have used MS SQL for many years and I am new to Oracle syntax and functions. I have taken over some stuff that has some less than desireable field structures Oracle. That being said maybe one of you experts can help me out and save me a tremendous amount of research time.

One of my tables has a field that contains a comma delimited number list i.e. '1,4,5,6,7,9' in a Clob field no less. I need to join this to a reference table to convert those Ids to their respective names. Is there any direct way to do this? Any best practice I should follow or look up.

Thank you all in advance for looking at this...

 

[justTryingToCode]

I forgot to mention this is in Oracle 10G

 

[Thargy]

Yes,

you must strip those magic numbers out of the clob and into separate fields (as many as it takes. Obviously, since they may or may not be essential as part of the clob they have to remain in the clob too.

You can't create referential integrity against a CLOB field, so don't waste your time trying. You need to find out the real-world meaning of those numbers, verify that the system contains suitable parent keys, and then add the columns needed to the CLOB-containing table.

This would be much easier if you could post create table statements and some sample data. Any chance thereof?

Regards

T