Adding zeroes or spaces to strings and get fixed length

Using the transformer stage, how do we get fixed length strings in output stream? In the below example, we see how to add zeroes and make the string of a given length.

Source having following data as below,





as in my target/ output must have:







How do we achieve this?

Str(‘0’,6-Len(string)): string 

How to find the Alphabetic Records in the column

In order to find the alphabetic records in the column, we have a Alpha Function. 

By using Alpha Function, we can find the number of alphabetic records we have in the required column. 

Take Job Design as below 

Seq.File—– Tx—– Filter—— D.s 

If we have a table as below 



Read and Load the data in the Sequential file 

In transformer Stage drag and drop the records to the Output file and create one column as new_names 

In Derivation write as alpha(e_name ) Click ok 

In Filter Stage— In Where Clause 

Write as new_names=1 

And don’t forget to drang and drop the records in Output 

Thats it click ok 

Compile and Run the Job to get required Output. 

Datastage String Functions

Function Usage
AlNum Cab be used to check if the given string has alphanumeric characters
Alpha TRUE if string is completely alphabetic
CompactWhiteSpace all consective whitespace will be reduced to single space
Compare Compares two strings for sort
ComparNoCase Compare two strings irrespective of Case in-sensitiveness
ComparNum Compare the first n characters of the two strings
CompareNumNoCase Compare first n characters of the two strings irrespective of case in-sensitiveness
Convert Replace character in a string with the given character.
Count Count number of times a given substring occurs in a string
Dcount Returns count of delimited fields in a string
DownCase Change all uppercase letters in a string to lowercase
DQuote Enclose a string in double quotation marks
Field Return 1 or more delimited substrings
Index Find starting character position of substring
Left Finds leftmost n characters of string
Len Length of the string or total number of characters in a string
Num Return 1 if string can be converted to a number
PadString Return the string padded with the optional pad character and optional
Right Finds Rightmost n characters of string
Soundex Returns a string which identifies a set of words that are
phonetically similar
Space Return a string of N space characters
Squote Covers a string into single quotation marks
Str Repeat a string
StripWhiteSpace Return the string after removing all whitespace
Trim Remove all leading and trailing spaces and tabs. Also reduce the internal occurrences of spaces and tabs into one.
TrimB Remove all trailing spaces and tabs
TrimF Remove all leading spaces and tabs
Trim Returns a string with leading and trailing whitespace removed
Upcase Change all lowercase letters in a string to uppercase