Datastage STRING FUNCTIONS in Transformer

In this article we are going to study all the Datastage STRING Functions one by one.

  •  Transformer is one of the most powerful stages in Datastage. This falls under the Processing category in the palette. Transformer stage is useful for doing transformation required as per particular aspect.
  • This stage neither extracts nor writes data (load) to a target database. It handles the extracted data and performs some business transformation and control the data flow. There are so many  functions, routines, parameters etc. to help for required transformations.

Let’s start with the Functions provide by Transformer first:

  1. String Function
  2. Logical Functions
  3. Date and Time Functions
  4. Mathematical Functions
  5. Null Handling Functions
  6. Number Functions
  7. Raw Functions
  8. Vector Function
  9. Type Conversion Functions
  10. Utility Functions

Let’s study them one by one.

Datastage String Functions:

#1. AlNum:
This functions check whether given string contains only and only alphanumeric
characters. I.e. if string contains any special characters it returns -1(False).
Example:If given string is ‘John$007’ then
AlNum (link_name.string_name) = -1 (false) as it contains special character ‘$’.
If given string is ‘John007’ then
AlNum (link_name.string_name) = 1 (True) as it contains only alphanumeric characters.

#2. Alpha:
This function checks whether thegiven string contains only alphabetic characters. I.e. from ‘Aa’ to ‘Zz’.
Example:If given string is ‘John007’ then
Alpha (link_name.string_name) =- 1 (False)
If given string is ‘John Margaret’ then
Alpha (link_name.string_name) = 1 (True)

#3. CompactWhiteSpace:
It just reduce the unnecessary white spaces between two words to single white space i.e. if given string contains ‘I    am   John’ then this function will give output ‘I am John’.

#4. Convert:
This function provides facility to change the content of string or whole string as a replacement. Example: If given string is ‘Jennifer’ and requirement is to change characters ‘nn’ to ‘NN’ then use we can use this function as follows
Convert(‘nn’,’NN’, link_name)

#5. Count:
It counts the number of times the given substring occurs in the string.
Example: If the given string is‘We are going really, really fast’. This function will give count 2 for substring ‘really’.
Count(link_name,’really’) =2.

#6. Dcount:
This functions returnscount of delimited fields in the string.
Example: If the given string is ‘john, ron, harry, rowling’ then this function will give count 3 for delimiter ‘,’ as shown below.
Dcount(link_name,’,’) =3

#7. Downcase:
This function is used change all the upper case letters in the string to lower case.
Example: If the given string contains ‘HorSeRiDiNg’ then this function will give
Downcase(link_name)=horse riding

#8. Dquote:
It puts the given string in double quotes.
Example: If given string is I am john, then this function returns
Dquote(link_name) =”I am John”

#9. Field:
This is important function which deals with the delimiter fields as it returns one or more substring depending upon the specified delimiters in a given string.
Example: Suppose given string is “adc89@nxcbs@nahs@ncn@xcz” where ‘@’ will be treated as a delimiter then
Field (link_name,’@’,1) = adc89
Field (link_name,’@’,1,3) = adc89@nxcbs@nahs
Field (link_name,’@’,2,2) = nxcbs@nahs

#10. Left:
This function returns the leftmost ‘n’ characters of string.
 Example: Suppose given string is “adc89@nxcbs@nahs@ncn@xcz” then
 Left (link_name, 3) = adc

#11. Len:
It returns the length of the given string.
 Example: if given string is “Chocolate” then
 Len (link_name) = 9

#12. Right:
This function returns the rightmost ‘n’ characters of string.
 Example: Suppose given string is “adc89@nxcbs@nahs@ncn@xcz” then
 Right (link_name, 3) = xcz

#13. Trim:
Trim is used to reduce additional space, tab or any unwanted occurrence of an character to one but it strictly removes the leading and trialing characters.This has the provision of additional argument to get desire result. We will see those with examples.


  1. If given string is   I am James Bond  then Trim (link_name) =I am James bond. It removes additional trailing and leading whitespaces.
  2. If given string is …I…am..James…Bond..then Trim (link_name, ‘.’) = It removes all leading, trailing dots with other reduced to one.
  3.  Trim (link_name, ‘.’,’A’)=IamJamesBond. It removes all dots.
  4.  Trim (link_name,’.’,’T’) = …I…am..James…Bond. Removes trailing dots only.

#14. TrimB:
It removes all trailing spaces and tabs from a string
TrimF: It removes all Leading spaces and tabs from a string
TrimLeadingTrailing:It removes all spaces and tabs from a string

20 Responses to “Datastage STRING FUNCTIONS in Transformer”

  1. Goli says:

    Very useful one, Thank you very much.

  2. Sam says:


    Is there also a string function that checks wheather the string only contains numeric characters?


  3. Vicky says:

    Very good one, came handy when I was looking around searching for a particular function.

  4. prasad says:

    alnum(input link) returns 0 if it fails not -1

  5. PrK says:

    Nice Work and Easy to Understand

  6. Lilantha says:

    You can use num(%value%) function to check whether contain numeric character

  7. Scott says:

    If I have a row with 700 characters, how do I extract the characters 2 through 9?

  8. rohit says:

    I get this weird charter Þ sometimes in first names or last names columns & I need to change it to Ñ

    Þ_ to Ñ

    Which Transformer function will be most efficient.


  9. jigu says:

    Very good and very useful to understand all each stage .

  10. Lokesh says:

    how to do this in tranformer , if a recordX having leading white spaces and alphanum and my req goes like intially i want 2 remove whitespaces then i need to check if n only if that recordX starts with a Num then i need that recordX to pass in target rest of them i need to rejaect

  11. Malli says:

    Is there any function to insert a delimiter in between a field of a coloum?

    Example:Input : IamMalli
    Output:I am Malli

  12. Jeff says:

    Finding the last occurrence of a delimiter and pulling the string to the right?


    Inputs: ‘Allen Price JR’
    ‘Cheryl K Smith MISS’
    ‘Davis,Jake DR’

    Outputs: ‘JR’

    I know I can use ‘ ‘ as a delimiter, and they are countable. But I need to find the last occurrence and capture the string to the right. Is there a way to do this? Thanks.

    • admin says:

      Hi Jeff, First you can use Dcount() function to count the number of delimiters. from your example ‘Allen Price JR’ Dcount function will give value as 2. Then use Field() function to get last field of sentence i.e. Field (LinkName.input,’ ’,2,1).
      Above field function will go to 2nd occurrence of delimiter and will fetch the 1 field which is ‘JR’.

      Hope this will help you

  13. preethi says:

    Hi i have a file like

    I want output like

    how do i get this output by using transformer stage.
    please give me reply fast

    • raj says:

      just use this derivation in transformer stage


      make sure to change the datatype to varchar

      hope this helps you.

  14. sasidhar says:


    As your requirement is ‘0’s before the string.First calculate the length of the column,according to that you can use


Leave a Reply

© 2018 Database ETL. All rights reserved.