SQL Statement Help

[chef]

I would gratefully receive any help regarding SQL query. I have a single field with a set of data stored in the field ‘DataSet’ that is delimited by | and follows the pattern of

product name|product code|version name|version code

per line with an unlimited number of lines

example entry in the DataSet field could be

first|12345|version745|99985
second|23456|ver666|68452
third|3456_00|v9864|7777

what I want to do with a SQL command is to first split the complete data set into lines and also into values i.e

tmpVal1 = first|12345|version745|99985
tmpVal2 = first
tmpVal3 = 12345
tmpVal4 = version745
tmpVal5 = 99985
tmpVal6 = 
   second|23456|ver666|68452
   third|3456_00|v9864|7777

i.e tmpVal6 is the remainder of DataSet that hasn’t been parsed. I then plan to set Dataset = tmpVal6 and loop through this until all rows are done.

I’ve got so far in that I know how to select tmpVal2,3,4,5

With help from google I've managed to work out how to retrieve the individual values, but Im stuck on the obvious bit

How do I improve the SQL statement below to store values tmpVal1 and tmpVal6? a colleague on his way out took a quick look and said "oh, use dual"??
but my SQL knowledge is very limited at best so excuse the stupidness of my request..

select 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
      ,substr(DataSet instr(DataSet,'|',1,2)+1
             ,instr(DataSet,'|',1,4)
             -instr(DataSet,'|',1,3)-1) tmpVal5
from (select DataSet aString from dual)

I'm gonna go grab some food and let my brain cool down before tackling it again.

Cheers

Chef

 

[lightng]

Sorry Chef, I don't understand what your trying to do. Is the data coming from one table and your trying to split it up and store in another table in but in different columns? Or are you splitting up the data into parts and doing something programatically with each row outside of SQL.

 

[Zippy]

WlightngS and is this a one-time problem? You seem to be using Oracle, which I know very little about. Sorry.
If it is a one-time problem could you export and re-import a delimited file?

 

[suityou01]

Write a function that takes a string (one row from your dataset) and returns a cursor.

SQL Server or Oracle? Then we can talk code.

 

[Durbs]

Edited:Removed my code - just looked at your requirement again and it wouldn't do what you want.

 

[chef]

wow, thanks all for the input.

@durbs, i'll look to see if that will help or atleast point me in the right direction, thank you for the input though.

i'll do my best to answer the other questions:

- yes it's oracle
- yes I will be using the variables outside of the database, im simply querying the database to get value's in the tmpVal fields that i can then loop through externally to get the results, i.e run the statement/function multiple times until all values have been parsed.
-the data all comes from an external source via webservices and ends up in the product/productcode/version/versioncode per line block of data in a single field called dataset, im trying to parse that into the 6 values so that if that part works i can then loop through the values until a null is returned and use the data as its set out of 1 row of data per line in the DataSet field
- i can't export and re-import the de-limited file as it isnt a 1 time problem but more of a solution to a shockingly bad designed interface of which i've fixed all but this final hurdle

@suityou, exactly what i was thinking but my limited knowledge of database queries/functions etc. is the reason why I was wondering if anyone here can here.

essentially i'll be running this externally, loop through the same function again, taking a line off each time and parsing it then using that data to push to various other forms etc. the looping is not a problem as that'll be done outside of the function/query, essentially i just need to reduce the main block of data by 1 line and parse it with each call of the function/query

 

[lightng]

Depending on the version of oracle, you can use regular expressions to pattern match each part of the string. Lookup REGEXP_INSTR for oracle. Oracle's implementation of regular expressions is a bit quirky but still they are much easier to read once you get used to them.

I would write the query for you but I'm about to nip out. I'm sure someone here could write one much quicker than I.

 

[suityou01]

wow, thanks all for the input.

@durbs, i'll look to see if that will help or atleast point me in the right direction, thank you for the input though.

i'll do my best to answer the other questions:

- yes it's oracle
- yes I will be using the variables outside of the database, im simply querying the database to get value's in the tmpVal fields that i can then loop through externally to get the results, i.e run the statement/function multiple times until all values have been parsed.
-the data all comes from an external source via webservices and ends up in the product/productcode/version/versioncode per line block of data in a single field called dataset, im trying to parse that into the 6 values so that if that part works i can then loop through the values until a null is returned and use the data as its set out of 1 row of data per line in the DataSet field
- i can't export and re-import the de-limited file as it isnt a 1 time problem but more of a solution to a shockingly bad designed interface of which i've fixed all but this final hurdle

@suityou, exactly what i was thinking but my limited knowledge of database queries/functions etc. is the reason why I was wondering if anyone here can here.

essentially i'll be running this externally, loop through the same function again, taking a line off each time and parsing it then using that data to push to various other forms etc. the looping is not a problem as that'll be done outside of the function/query, essentially i just need to reduce the main block of data by 1 line and parse it with each call of the function/query

Oracle supports regular expressions, which helps. Take a look at

http://www.oracle.com/technology/oramag/code/tips2007/070907.html

regexp_substr(str, '[^|]+', 1, level)

 

[lightng]

REGEXP_SUBSTR even. Looks like SY01 has got it. Got to go. Bye.

 

[chef]

you guys are geniuses, genieie, however its spelt, many thanks... i'll investigate immediately..