SCAN (x,y)

Description:

This function finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text. This function is case sensitive and doesn't allow wildcard characters.

Syntax: SCAN (find_text, within_text) ; or
             SCAN (find_text, within_text, start_num)

• Find_text : is the text you want to find.
• Within_text : is the text containing the text you want to find.
• Start_num : specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

Remarks

• The text arguments are compulsory while the Start_num argument is optional.
• If you specify a number instead of a text value as this parameter, the function would return an error which is #VALUE
• If you specify a "Find_Text" string value which doesn't exist in the "Within_Text" string value or if the case of the two arguments doesn't match, then an error is returned which is #Num.

Example

The SCAN function can be used in any database where you have stored any text values and you want to check if one text value exists within another value and matches that portion exactly. The function returns a number that matches the first position where the "Find_Text" exists within the "Within_Text".This function returns this number value as its result after the function computes the two or three parameters supplied to this function and after the function determines whether or not the first value is found in the other.

=SCAN ("A", "ABC")	-equals 1
=SCAN ("N", "ANY")	-equals 2
=SCAN ("90", "1900")	-equals 2

 

Note: A number supplied within double quotes is taken as a string parameter.

 
 

Let us take the example of a database in PowerOLAP where we have certain text values for name, last name, city, country etc., and we need to manipulate these text values and arrive at results with different permutations and combinations. We shall use this example in this case to ascertain whether or not there exists the "Find_text" within the "Within_text" which are supplied by the two parameters provided by one text value and one member name called "Name", which is the parameter that has the values for people's names. In this case, we have written a formula in the appropriate cube in PowerOLAP, pulling in the value from the above mentioned parameters and then checking for the match between the two values. A number value is returned as the result for the SCAN function.



The formula above writes values into the "StringDataFunctions" cube to the "String Data Manipulations" dimension into the member named "SCAN" by calculating the appropriate value for this member, based upon the parameters that are given as input to this function, namely the text value, an alphabet "P" and the "City" member. The result is returned only if the first parameter string exists somewhere within the second parameter, and the position where the match is first found, is the number returned by the function.