SUBSTR for mutibyte.

[nab03]

Hi ALL.. Im nab at japan.
I have a question.
Im using Informatica PowerCenter7.1.2.
I want to convert from my textfile to flat file as CSV using "SUBSTR" function with multibyte.

for example ## XXX is multibyte chara(Shift_JIS)
--------my txt file-----
No | country | name |desc
1|japan|nab matsui|XX6X132-6
2|USA|ichiro suzuki|XX88X54-44

-----------result------------
No,country,name,desc
1,"japan","nab matsui","XX6X1" <- 8byte
2,"USA","ichiro suzuki","XX88X" <- 8byte

so..
I want to get desc field as 8byte..
I tried using "SUBSTR". like this..
"SUBSTR("XX6X132-6",0,8)"
but this result was "XX6X132-".
I need "XX6X1" as result.

How can I do this?
please let me know how to get "XX6X1".
any idea?

 

[blom0344]

"SUBSTR("XX6X132-6",0,5)"

Ties Blom

 

[nab03]

Hi Blom. Thanks for your prompt reply.
I have to give you more information.

>"SUBSTR("XX6X132-6",0,5)"
I know this is right.
But how about this case.. if data is "XXXX132-6".

if i wrote "SUBSTR("XXXX132-6",0,5)",
this result is "XXXX1".

I hope I can get "XXXX". <- 8byte

#X is 2byte, number is 1byte.
I hope you understand above.
and thank you for reading my poor english.

 

[blom0344]

nab,

you need to build a scenario using IIF statements to check where the input string reaches the 8 byte threshold.

This is not very complicated, but the expression will be pretty extensive.

First step would be to create ports (variables) that calculate a numeric value for each of the substrings within the string (whether a character from the string is either X or not

L8:
IIF((substr(field),0,1),'X',2,1)+
IIF((substr(field),1,1),'X',2,1)+
IIF((substr(field),2,1),'X',2,1)+
IIF((substr(field),3,1),'X',2,1)+
IIF((substr(field),4,1),'X',2,1)+
IIF((substr(field),5,1),'X',2,1)+
IIF((substr(field),6,1),'X',2,1)+
IIF((substr(field),7,1),'X',2,1)

L7:
IIF((substr(field),0,1),'X',2,1)+
IIF((substr(field),1,1),'X',2,1)+
IIF((substr(field),2,1),'X',2,1)+
IIF((substr(field),3,1),'X',2,1)+
IIF((substr(field),4,1),'X',2,1)+
IIF((substr(field),5,1),'X',2,1)+
IIF((substr(field),6,1),'X',2,1)

L6:
IIF((substr(field),0,1),'X',2,1)+
IIF((substr(field),1,1),'X',2,1)+
IIF((substr(field),2,1),'X',2,1)+
IIF((substr(field),3,1),'X',2,1)+
IIF((substr(field),4,1),'X',2,1)+
IIF((substr(field),5,1),'X',2,1)

L5:

IIF((substr(field),0,1),'X',2,1)+
IIF((substr(field),1,1),'X',2,1)+
IIF((substr(field),2,1),'X',2,1)+
IIF((substr(field),3,1),'X',2,1)+
IIF((substr(field),4,1),'X',2,1)

L4:

IIF((substr(field),0,1),'X',2,1)+
IIF((substr(field),1,1),'X',2,1)+
IIF((substr(field),2,1),'X',2,1)+
IIF((substr(field),3,1),'X',2,1)

One of these ports will have a value 8.

The port that holds value 8 will indicate the final SUBSTR expression.

Ties Blom

 

[nab03]

Hi Blom. Thanks for your reply.
and Sorry for my slow reply.
I have to give you more information again.

>IIF((substr(field),1,1),'X',2,1)+
>IIF((substr(field),2,1),'X',2,1)+
>IIF((substr(field),3,1),'X',2,1)
it's nice idea. but I can't.

because The Japanese language has too many kind of 2byte letters.
X is not 1 kind.

if i wrote "SUBSTR("XPQX132-6",0,5)",
I hope I can get "XPQX". <- 8bytes
->X,P and Q is 2byte, number is 1byte.

how can I get this 8bytes
Is this impossible?

I hope you give me more idea. Thank you.
nab.

 

[blom0344]

It should still be possible, but it is just a bit more complex.
What you should attempt is to do a forecheck whether part of the string is numeric or not.
If you can convert part of the string to a numerical then assign it with value 1, in all other cases assign it value 2.
Powercenter has the IS_NUMBER function to test which works okay on strings.

You might think of writing a stored procedure at database level that does these checks for you. Looping through data/strings is appropriate there.

adjusted solution in Powercenter:

L8:
IIF((IS_NUMBER(substr(field),0,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),1,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),2,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),3,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),4,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),5,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),6,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),7,1)),0,2,1)

L7:
IIF((IS_NUMBER(substr(field),0,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),1,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),2,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),3,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),4,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),5,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),6,1)),0,2,1)

L6:
IIF((IS_NUMBER(substr(field),0,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),1,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),2,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),3,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),4,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),5,1)),0,2,1)

L5:

IIF((IS_NUMBER(substr(field),0,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),1,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),2,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),3,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),4,1)),0,2,1)

L4:

IIF((IS_NUMBER(substr(field),0,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),1,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),2,1)),0,2,1)+
IIF((IS_NUMBER(substr(field),3,1)),0,2,1)

Ties Blom

 

[nab03]

Hi Blom. Thanks for your promt reply and great help.
you are so smart and very kind of me.

I'll try this complex way.

nab