Saturday, May 12, 2012

SUBSTR function in Sql

The SUBSTR functions allows you to extract a substring from a string. The syntax for the substr function is:
SUBSTR ( string, start_position, [ length ] )
Where
  • string is the source string.
  • start_position is the position for extraction. The first position in the string is always 1.
  • length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.

Note:
  • If start_position is 0, then SUBSTR treats start_position as 1 (ie: the first position in the string).
  • If start_position is a positive number, then SUBSTR starts from the beginning of the string.
  • If start_position is a negative number, then SUBSTR starts from the end of the string and counts backwards.
  • If length is a negative number, then SUBSTR will return a NULL value.

For Example:
  1. SUBSTR ('This is a Informatica blog', 11, 11) return 'Informatica'
  2. SUBSTR ('This is a Informatica blog', 11) return 'Informatica blog'
  3. SUBSTR ('This is a Informatica blog', -4, 4) return 'Blog'
  4. SUBSTR ('This is a Informatica blog', -16, 4) return 'Info'

1 comment:

  1. I am William..
    I just browsing through some blogs and came across yours!
    Excellent blog, good to see someone actually uses for quality posts.
    Your site kept me on for a few minutes unlike the rest :)
    Keep up the good work!Thanks for sharing a important information informatica

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...