Hi,
I have a text file with two columns named TABLENAME and CLOB. I have to extract the information from them as follows :
TABLENAME CLOB
HEADER(first row) PROVIDER12082015
TRAILER(last row) 00000000669
If I have three columns in the transformer (HeadName, HeadDate, RecCount), how would I put this in the derivations?
HeadName is supposed to be equal to PROVIDER (from CLOB)
HeadDate is supposed to be equal to 12082015 (from CLOB)
RecCount is supposed to be equal to 00000000669 (from CLOB)
Extracting Characters from a row
Moderators: chulett, rschirm, roy
Extracting Characters from a row
SCOTTDun
Welcome.
Not sure why people would be storing small strings in a CLOB field, is there more to it than that or is that literally the entire contents? I'd also be curious what requirements you were given, any direction on how to accomplish this.
What database are we talking about? They typically come with functions to query CLOB fields so you may need to leverage them in your source SQL. Or if you know you never need more than X bytes from the CLOB you can cast it to a string when you select it.
Not sure why people would be storing small strings in a CLOB field, is there more to it than that or is that literally the entire contents? I'd also be curious what requirements you were given, any direction on how to accomplish this.
What database are we talking about? They typically come with functions to query CLOB fields so you may need to leverage them in your source SQL. Or if you know you never need more than X bytes from the CLOB you can cast it to a string when you select it.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Ah, never mind. "I have a text file" so there's no actual CLOB involved, just a string field with that name. You really need to supply more information so we don't have to guess at some of these answers. For example, you mention "first row" and "last row"... what happens to all of the other rows? Are you having a problem identifying first and last or you're handling that and this is literally about how to "extract characters"? Going to assume the latter since you've already posted this as sdeuces90 on the Toolbox forums.
As noted there, it's just a substring. If the first 8 characters are PROVIDER then you can take those for the HeadName and then the next 8 for the HeadDate. For the trailer, is the size of the digits fixed or do you need to find the end of it, say where the first space is? If you're having trouble with specific derivations, post them here.
![Wink :wink:](./images/smilies/icon_wink.gif)
As noted there, it's just a substring. If the first 8 characters are PROVIDER then you can take those for the HeadName and then the next 8 for the HeadDate. For the trailer, is the size of the digits fixed or do you need to find the end of it, say where the first space is? If you're having trouble with specific derivations, post them here.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: