Extracting Characters from a row

ScottDun · Post by **ScottDun** » Thu Dec 10, 2015 9:57 am

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)

chulett · Post by **chulett** » Thu Dec 10, 2015 10:13 am

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.

ScottDun · Post by **ScottDun** » Thu Dec 10, 2015 10:20 am

The CLOB has 700 characters in it. The info I put out there are the first characters in the column itself. I need to extract those in my transformer but I am having a time with it.

chulett · Post by **chulett** » Thu Dec 10, 2015 10:36 am

Database?

chulett · Post by **chulett** » Thu Dec 10, 2015 11:19 am

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.

ray.wurlod · Post by **ray.wurlod** » Thu Dec 10, 2015 2:10 pm

Maybe Field() function or substringing to extract the header, trailer and the remainder.

chulett · Post by **chulett** » Thu Dec 10, 2015 3:28 pm

True... forgot to mention that... possibly depending on what else is in the string.