format string
Moderators: chulett, rschirm, roy
format string
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
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
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:
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
Last edited by kcbland on Wed Jun 07, 2006 11:31 am, edited 2 times in total.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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
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.
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
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.
Last edited by DSguru2B on Wed Jun 07, 2006 11:32 am, edited 2 times in total.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Who needs a routine?
OK, this answer hinges on knowledge about how the Fmt() function works, but it's all in the DataStage BASIC manual.
Code: Select all
Field(Fmt(Left(InLink.TheString, 132), "132T"), @TM, 1, 1)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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 ?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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
thank you
Re: format string
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.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
Due to the confusion with the logics, its rather difficult for me to comprehend what is your true need, hence devising a solution.
Regards,
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 47
- Joined: Wed Apr 12, 2006 12:13 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.