format string

[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

[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

[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.

[DSguru2B]

Again Ken, you beat me to it.
I need to be more quick next time
Its really good from "the top of your head". Let me have some of what your having

[sainath]

hi
Thank you very much for your quick reply.
thks

[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.

[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

[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.

[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

[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 ?

[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

[DSguru2B]

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,

[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

[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

[sylvan_rydes]

Thanks a lot