Page 1 of 1
format string
Posted: Wed Jun 07, 2006 11:06 am
by sainath
Hi
I have a input colunm with varchar2(255).but in my out put column length is varchar2(132).I have to insert first 132 characters and if it exceeds this length back trace to last space and move the remaining entire text to next line.
pl let me know how we can do this in datastage.
thks
Posted: Wed Jun 07, 2006 11:27 am
by kcbland
You can use the LENGTH function to get the number of characters in a string. INDEX finds the character location of a substring within a string. COUNT counts the number of characters.
Here's some code off the top of my head:
Code: Select all
If LENGTH(yourstring) <= 255 Then
FirstLine = yourstring
SecondLine = ""
End Else
First255=yourstring[1,255]
If RIGHT(First255,1)=" " Then
FirstLine = First255
SecondLine = ""
End Else
NumberOfSpaces=COUNT(First255, " ")
FirstLine=yourstring[1,INDEX(yourstring, " ",NumberOfSpaces]
SecondLine=yourstring[INDEX(yourstring, " ",NumberOfSpaces,LENGTH(yourstring)]
End
End
Posted: Wed Jun 07, 2006 11:27 am
by DSguru2B
So do you have only one column coming in from the file. Because if thats the case then you can build a small routine. Something like this
Code: Select all
Str = Trim(Arg1)
If len(Str) =< 132
Then
Ans = Str
End
Else
Ans = Left(Str,Index(Str," ",DCOUNT(Left(Str,132)," ")-1)):char(10):Str[Index(Str," ",DCOUNT(Left(Str,132)," ")-1),len(Str)]
End
Its a little bit confusing. Basically you are checking if the length of the incoming data. If its less than or equal to 132 then everybody's happy. Just return it.
If its greater than 132 then you are taking the first 132 characters and counting the spaces in it. Thats what DCOUNT() is doing.
The index function will get the position of that space whick will be used to get the string from the poistion 1 uptill that particular space.
Than i am concatenating it will char(10) which will put in a new line and then i am getting the rest of the string and performing a concatenation.
But my solution is only going to work for a single column.
Posted: Wed Jun 07, 2006 11:29 am
by DSguru2B
Again Ken, you beat me to it.
I need to be more quick next time
![Twisted Evil :twisted:](./images/smilies/icon_twisted.gif)
Its really good from "the top of your head". Let me have some of what your having
![Wink :wink:](./images/smilies/icon_wink.gif)
Posted: Wed Jun 07, 2006 11:42 am
by sainath
hi
Thank you very much for your quick reply.
thks
Posted: Wed Jun 07, 2006 3:55 pm
by ray.wurlod
Who needs a routine?
Code: Select all
Field(Fmt(Left(InLink.TheString, 132), "132T"), @TM, 1, 1)
OK, this answer hinges on knowledge about how the Fmt() function works, but it's all in the
DataStage BASIC manual.
Posted: Fri Jun 09, 2006 1:14 pm
by sainath
Hi
I worked with routine it worked fine.i just added while loop to it.
now the problem is how to tell the datastage to insert this 10 lines each 132 char in length into a line,next lines.. in table .
I am getting warning value too large for column.
share your veiws.
thks
Posted: Fri Jun 09, 2006 1:41 pm
by DSguru2B
You mean same record, but put a line terminator within tht column. That will not work with the length specified for that column. You are better off increasing the length of the column.
Posted: Fri Jun 09, 2006 2:07 pm
by sainath
HI
i mean depend on how many lines you have in Input (say 10 ) i have to generate 10 lines by creating line numbers and increasing line number by 1 in o/p and then insert each line to correspondong line in O/p.
thks
Posted: Fri Jun 09, 2006 2:13 pm
by DSguru2B
Ok i got the part of splitting the data if the length is larger than 132. But i am sure there must be more columns with that column. How will they be treated. Can you give some sample input data and how it should look like in the output ?
Posted: Fri Jun 09, 2006 2:40 pm
by sainath
Code: Select all
HI
This is my dample data .
key inputcolumn
k1 abc
def
ghi
k2 jkl
Code: Select all
o/p
linenumber o/pcolumn
1 abc
2 def
3 ghi
1 jk1
[/code]
thank you
Re: format string
Posted: Fri Jun 09, 2006 10:13 pm
by DSguru2B
sainath wrote:Hi
I have a input colunm with varchar2(255).but in my out put column length is varchar2(132).I have to insert first 132 characters and if it exceeds this length back trace to last space and move the remaining entire text to next line.
pl let me know how we can do this in datastage.
thks
Depending upon your original question, the input column was of length 255 and your target was 132. That means maximum it could be divided into two rows. And now your sample data shows otherwise. Is this a different logic? If so please start a new thread instead of continuing in the same thread with different logics. The solution to your original logic was given by two routines and one derivation. Those solutions will not work with your new requirement.
Due to the confusion with the logics, its rather difficult for me to comprehend what is your true need, hence devising a solution.
Regards,
Posted: Wed Jun 21, 2006 4:37 pm
by sylvan_rydes
Hi everyone,
This is one simple and silly question. I am trying to generate a random string with random number of alphabates. I have the max legth of string. But I don know how to generate random string. I know that Rnd can be used but how?
Thanks in advance.
Sylvan
Posted: Wed Jun 21, 2006 4:48 pm
by ray.wurlod
You need a routine.
Code: Select all
FUNCTION RandomString(Length)
* These settings limit the characters to upper case alphabetics in ASCII.
* Adjust as required.
Equate MINCHAR To UniSeq("A")
Equate MAXCHAR To UniSeq("Z")
Ans = ""
For C = 1 To Length
Ans := UniChar(Rnd(MAXCHAR-MINCHAR)+MINCHAR)
Next C
RETURN Ans
Posted: Wed Jun 21, 2006 5:50 pm
by sylvan_rydes
Thanks a lot