esProc Advanced Coding: Creative Usage of Strings

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 results which act as macro string values. For example:

B1 gets an arithmetic operator randomly from the operators list in A1; A2 and B2 generate randomly two integers less 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 dynamically according to different signs.
Please note that if a macro is used in a string enclosed by double quotation marks, the replacement will not be executed. For example:

${A1} in A2 hasn’t been replaced, and the result in A2 is still the string in quotation marks:

In A3, the left double quotation marks are changed to another macro, and then the two macros are outside the quotation marks. Now the macros are validated and can be used to construct the expression =”aabbA”+”Accdd”. 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, as well as various functions according to names. 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, so as to 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, which is the result when the macro is first replaced.

It should be clear that macros are used for generating expressions, 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. It can achieve functions similar to those of a self-defining function when it uses parameters in the generated 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 lengths of the three sides of a triangle. ?1, ?2 and ?3 respectively represent the 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 “…” have similar usages. 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 usage 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 quotation mark ” 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 along with different editing operations, and the expression can be adjusted during operations like copy, paste, etc. Therefore, when a string constant returns a cell name as needed, these characters of it 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 nested macros in string constants. 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 cellset will be like this:

It can be seen that the cell names in expressions in both B3 and B4 change accordingly. As a string represented by “…” cannot use macros, and the content of “…” will not change along with the editing operation, it is not applicable in certain cases.