September 27, 2014 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, AB1 Aim2 Abnv5 1An8bx as in my target/ output must have: 000AB1 00Aim2 0Abnv5 1An8bx How do we achieve this? Str(‘0’,6-Len(string)): string
June 25, 2012 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 alpha_tabl e_id,e_name 11,james 22,leana121 33,lacky11 44,tom 55,venu 66,emy211@ 77,tiny@ 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.
June 20, 2012 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 optionallength Right Finds Rightmost n characters of string Soundex Returns a string which identifies a set of words that arephonetically 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