Main » 2012 » October » 10 » Make fixed length delimited string into multiple rows in oracle.
5:48 PM
Make fixed length delimited string into multiple rows in oracle.
Again something i struggled with myself and
found no help with online.
We will need to use a clever little function in oracles bag of tricks, the
hierarchical query.
Hierarchical queries allow you to create a query on a table where the query
will link back to the same table any number of times. I wont go too in depth
about these themselves as their are some quite good webpages explaining them
that are easily found on google.
How do these queries allow you to change a delimited column into multiple rows?
Well we are miss using the function really, it is not truly intended for this
use but the function lends it self very nicely to this situation.
There are plenty of examples on oracles forums (if you can pick through them)
of using this type of query to great effect on character delimited strings. On
implementing against actual data and adapting to a fixed length rather than
delimited i found a lot of problems. Effectively once we took the examples
beyond one concatenated string. The results became geometric, growing by a
factor of the original row number with every iteration. So an initial sample of
3 rows with delimited columns would give 9 rows after just 3 iterations.
The solution was quite simple, as we have fixed length data (in my case 366
sets of 2 characters,) instead of using the actual data in the hierarchical
query we can simply use the hierarchical query to create a table to which we
link our data. this table will hold sufficient information to allow a simple
select to filter the data to what we need.
For example.
select
x.id,
x.startDate,
x.startDate + (p.position - 1) as
att_date,
substr(x.delimedString, (p.position * 2) -
1, 2) as val,
from table1 x,
(select rownum as position
from dual
connect by level < 366) p
where p.position <= (length(x.delimedString) / 2)
As you can see we have made every row in table1 into 365 rows. These rows will contain all of the information plus a rownum value. We use this rownum value in the select clause, to make each val value different. So the row with position 1 will take the first 2 charecters. The row with position as 3 will take the 5th and 6th numbers.
Wow, marvelous blog structure! How long have you ever been blogging for? you made running a blog look easy. The total glance of your web site is wonderful, as neatly as the content material!
Hello dear friend, I would like to offer placement of your link (or links) on different platforms of the internet such as: forums, blogs, comments and much more. . .
Increase your Visibility Boost Your Seo Rank - Get Organic Traffic From Google. Ranking in Google isn’t hard. All you need is a healthy number of backlinks from referring domains that have authority and trust in Google’s eyes.
This Backlinks Service Benefits:
1. Easily get Google rankings
2. Get a lot of traffic from Google
3. You can earn from the website in different ways
