MID (x,y,z)

Description:

This function MID returns a specific number of characters from a text string, starting at the position you specify and based upon the number of characters you specify.

Syntax: MID (Text, Start_num, Num_chars)

• Text : is the text string containing the characters you want to extract.
• Start_num : is the position of the first character you want to extract in text. The first character in text has start_num 1, and so on.
• Num_chars : specifies the number of characters you want MID to return from text.

Remarks

• Num_chars and the start_num must be greater than zero. If either of the parameters are equal to 0 or less than 0 then PowerOLAP returns a #Num error.
• All arguments are compulsory and cannot be omitted.
• If num_chars is greater than the length of text, MID returns all of the text that falls after the specified "start_num".
• If you specify a number instead of a text value as this parameter, the function would return an error which is-#VALUE.

Example

The MID function can be used in any database where you have stored any text values and you want to extract some part from the beginning, the middle or the end that text value. The function extracts the value beginning the counting from the "start_num" parameter and it extracts the number of characters from this position until the number specified in the "num_chars" parameter. The function returns the extracted text as the result of the function. This function returns this text value as its result after the function computes the two parameters supplied to this function and after the function determines the value of the characters as per the numbers specified as input to the function.

=LEFT ("Pennsylvania", 2, 4)	-Four characters from 2nd position ("enns")
=LEFT ("Switzerland", 8, 4)	-Four characters from 8th position ("land")

 

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 find the characters with the help of the MID function deriving text from the text which is the "Input" for this cube, which in this instance is the member "State". Here we have written a formula in the appropriate cube in PowerOLAP, pulling in the value from the above mentioned parameters and then calculating the values to be extracted with the MID function. A text value is returned as the result for the MID function.



The formula above writes values into the "StringDataFunctions" cube to the "String Data Manipulations" dimension into the member named "MID" by calculating the appropriate value for this member, based upon the parameters that are given as input to this function, namely the reference for the "State" member and the start_num parameter "3" which starts extracting from the 3^rd character and extracts until 4 it reaches the count of 4 characters which is the "num_chars" parameter also specified. The result returned is this extracted value as text.