Re: Column seperator in Sql/Plus

watuni2000_at_yahoo.co.nz (Tyler Smith Watu) wrote in message news:<2f3438a4.0401211158.3a057d6f_at_posting.google.com>...
> I will like to be able to seperate the columns based on a comma
>
> For example given .............
> Tyler,,COMP1,prd,main.exe
> Smith,Smith,COMP23,dev.exe
> Watu,,COMP7,prde.exe
> ********************************
>
> I will like to select the first column and the third column seperated
> by ","
> The query should return
>
> Tyler COMP1
> Smith COMP23
> Watu COMP7
>
> Thanks in advance for your assistance
>
> TY

What version of Oracle and where is this data?

If the data was in a file you could use sqlldr to load it into a table and then select the first and third columns or define an external table for the file and just select the first and third columns.

If the data is stored as one column in a table already and extracting it an using either of the two variations above is not desired then you might try a combination of the substr and instr functions. The first column ends with the first comma encountered so substr(column,1,instr(column,',',1,1) - 1) should find it. {Check SQL manual for correct parameter list) The third column would exist between the third and forth commas so instr(column,'c',1,3) would be the starting position and instr(column,',',1,4) would be one postition past the ending position.

I am sure you get the idea. But this solution is dependend on all the data matching your sample and may not work for all of your actual data. Also I do not have a system available to use to produce working code so stupid errors may exist.

HTH -- Mark D Powell -- Received on Wed Jan 21 2004 - 18:43:59 CST