SQL Statement Help

[Spacecadet]

in SQL Server you can use XML to solve this:

declare @dataset varchar(1000)
declare @dataxml xml
 
set @dataset ='first|12345|version745|99985
second|23456|ver666|68452
third|3456_00|v9864|7777'
 
select @dataxml =cast('<dataset><row><data>'+replace(replace(replace(@dataset,char(13),''),'|','</data><data>'),CHAR(10),'</data></row><row><data>')+'</data></row></dataset>'as xml)
 
select dataset.a.value('data[1]','varchar(100)')as data1,
         dataset.a.value('data[2]','varchar(100)')as data2,
         dataset.a.value('data[3]','varchar(100)')as data3,
         dataset.a.value('data[4]','varchar(100)')as data4
from @dataxml.nodes('dataset/row')as dataset(a)

as long as the number of columns is static, you're fine. It'll handle any number of rows.

There should be similar functionality in Oracle

[DimPrawn]

select substr(dataset, 1, instr(dataset, chr(10)))
      ,substr(dataset, 1, instr(dataset, '|') - 1) tmpVal2
      ,substr(dataset, instr(dataset, '|') + 1
             , instr(dataset, '|', 1, 2)
             - instr(dataset,'|', 1, 1) - 1) tmpVal3
      ,substr(dataset, instr(dataset, '|', 1, 2) + 1
             ,instr(dataset, '|', 1, 3)
             -instr(dataset, '|', 1, 2) - 1) tmpVal4 
      ,nvl(substr(dataset, instr(DataSet, '|', 1, 3) + 1
             ,instr(dataset, chr(10), 1, 1)
             -instr(dataset, '|', 1, 3) - 1), substr(dataset, instr(DataSet, '|', 1, 3) + 1)) tmpVal5
      , substr(dataset, instr(dataset, chr(10)))  TmpVal6          
from (select 'first|12345|version745|99985
second|23456|ver666|68452
third|3456_00|v9864|7777' as dataset from dual)

.

Jeez that looks horrific. The sort of thing you find with no comments in a stored proc named usp_do_work which is critical to some Big Co. data mangling process which worked for 6 months but now fails completely.

Please, don't use SQL for this sort of thing.

[Spacecadet]

Jeez that looks horrific. The sort of thing you find with no comments in a stored proc named usp_do_work which is critical to some Big Co. data mangling process which worked for 6 months but now fails completely.

Please, don't use SQL for this sort of thing.

even though it's been a few years since I did any serious Oracle, that SQL it makes perfect sense to me

Of course, you shouldn't let inexperienced coders near Big Co. data mangling process

[doodab]

It is a bit fugly isn't it

[DimPrawn]

even though it's been a few years since I did any serious Oracle, that SQL it makes perfect sense to me

Of course, you shouldn't let inexperienced coders near Big Co. data mangling process

Yeah yeah. I wonder why this sort of work has gone to India......

[doodab]

I wonder why this sort of work has gone to India......

Because you charge too much?

[Spacecadet]

Yeah yeah. I wonder why this sort of work has gone to India......

cause you're too dim to do it?