Wednesday, December 22, 2010

Difference of SCAN and SUBSTR

  • SCAN extracts words within a value that is marked by delimiters.

  • SUBSTR extracts a portion of a value by starting at a specified location. 
The SUBSTR function is the best choice to extract class level information from ID. By contrast, the SCAN function is best used when

  • you know the order of the words in the character value
  • the starting position of the words varies
  • the words are marked by some delimiter. 


General form, SCAN function:
SCAN(argument,n,<delimiters>)
where
  • argument specifies the character variable or expression to scan.

  • n specifies which word to read.

  • delimiters are special characters that must be enclosed in single quotation marks (' '). If you do not specify delimiters, default delimiters are used.
General form, SUBSTR function:
SUBSTR(argument,position,<n>)
where
  • argument specifies the character variable or expression to scan.

  • position is the character position to start from.

  • n specifies the number of characters to extract. If n is omitted, all remaining characters are included in the substring.

2 comments: