Creative Use of Strings in esProc

In esProc, strings are not merely a data type; they can also be used as macros or string constants to construct expressions, or employed by eval() function to generate expressions dynamically as needed.

1. Macro

Macros in esProc refer to strings for composing expressions,only that these strings are usually generated dynamically.Macros are values of parameters or computed results of expressions. In practical use, a macro should be enclosed by signs ${}.esProc will first compute the expression enclosed by ${…}and then replace ${} with computed resultwhich acts as macro string values. For example:

B1 gets an arithmetic operator randomly from the operators list in A1; A2 and B2 generate randomly two integersless than 1,000, then A3 gets the arithmetic result. 

Results of B1, A2 and B2 are as follows:

So, what A3 gets is the result of computing 79*994:

In this case, ${B1}is the macro used to construct the expression according to the change of signs.

Please note that if a macro is used in a string enclosed by double quotation marks, replacement by the macro will not be executed. For example:

${A1} in A2 doesn't work, and the result in A2 is the string in quotation marks:

In A3, the left quotation mark is changed to another macro, and then the two macros are without the quotation marks. Now the macros are validated and can be used to construct the expression ="aabbA"+"Accdd", whose computed result in A3 is as follows:

It can be seen from the above example that, different from string parameters, macros are part of the expressions, and usually play a role in dynamically generating expressions.

Often, Macros are used to call dynamically different cell values according to names, as well as various functions. They can also employ dynamically various parameters and do some other jobs. Macros are really a convenient choice because these jobs are difficult to be done by using fixed expressions. For example:

In A6, find the row in which the first record satisfying the condition of POPULATION >5,000,000 is located; with the expression in A7, get the name of the corresponding cell in column B according to the row number, and then get the abbreviation of the state. Please note that the expression in the macro must return a string, therefore, string() function is needed in A7 to convert the row number in A6 into a string when the macro is used to generate the cell name.Results of A6 and A7 are as follows:

Also please note that if a macro is used in a cell, it will be parsed only in its first use. Therefore macros are not suitable for loop. For example:

Here A6 returns rows in which all records whose population is greater than 5,000,000 are located:

A7 tries every time to get the abbreviation of corresponding state according to the sequence loop in A6. Results are as follows:

As strings in the macro are only converted when the expression is first parsed, all abbreviations of the states after loop are AZ.

It should be clear that macros are used for generating expression, not for generating command statements, like for, func, etc.

2 eval function

eval(x, …) function in esProc can convert strings computed by x into an expression and compute its value.Its usage is similar to that of a macro, for both of them can generate an expression.Their difference lies that a macro can only convert a partial expression while eval function can generate a new expression during computing and return the computed result, and use parameters in the expression.
Since eval function needs to generate an expression in computing, the computed result of expression x must be strings. For example:

Still, an arithmetic expression is generated randomly and computed result can be seen in A3.

Results of B1, A2 and B2 are as follows:

In A3, it can be seen that the use of eval() function is different from that of the macro in the above. At this point, the result of A3 is that of computing 893+466:

eval function is more powerful than the macro, which can achieve functions similar to those of a self-defining function when using parameters during generating an expression. For example:

A1 computes the number of members that are greater than 5 in a sequence by using eval function. The question mark ? in the expression generated by eval represents a parameter. A2 judges whether the three parameters qualify as the length of three sides of a triangle, in which ?1, ?2 and ?3 respectively representthe first, the second and the third parameter.Results of A1 and A2 are as follows:

In using eval function, the generated expression will be parsed each time it is computed, so it can be used by loop. For example:

Now the results in A7 are what we want:

3.String constants

The string constants being discussed are neither the strings simply enclosed by double quotation marks, nor the string-typed constants, they are the strings represented by $[…].
$[…] and "…" are similar in usage. Both of them refer to strings represented by…in expressions. For example:

Computed results in A1and A2 are as follows:

It can be seen that the use of $[…] is basically the same as that of "…". Strings enclosed by both of them use a backslash \ as the escape character to mark ambiguous characters, like the single quotes " in a common string and right bracket]in a string constant.

In fact, the string constant $[…] is often used by macros and eval function. In the esProc's IDE, the cell name in the expression in $[…] will change accordingly along with editing operation, and adjust the expression during operations like copy, paste, etc. Therefore, when a string constant returns a cell name as needed, these characters can prevent expression errors if there is any change in cellset structure. For example: 

Both B2 and B3 generate query function's query strings with cell values by using a macro and a string simultaneously. B2 queries states whose population is greater than 10,000,000:

B3 queries states whose population is greater than 10,000,000 and whose abbreviations begin with letter C:

Now if an empty row is inserted before the second row, the cell set will be like this:

It can be seen that the cell names in expressions in both B3 and B4 change accordingly. As "…"cannot use macros, and its contentwill not change accordingly along with the editing operation,it is not applicable in certain cases.