esProc Divides Letters into Segments

The RBP_VALUE field in the rbd_dtl table stores the string “all” and some letters. We want to convert values in the field to strings of segments. The desired result is A-F,H,J-K,N-O,Q-S,U-V,X-Z.

Below is the original data：

Rules to be followed are：

If RBP_VALUE= “ALL”, then discard the value;

According to the alphabetical order if values of RBP_VALUE from one letter to another one are consecutive, then make them a segment with both the first and last letter included and “-” between them, like “A-F”;

If two values of RBP_VALUE are inconsecutive, like K and N between which L and M are absent, then unite the former with the letter or letters before it and join the latter with the letter or letters after it. Separate two segments with a comma. This is like “J-K”, “N-O”.

If a value in RBP_VALUE has not consecutive letters both before and after it, then take it as an individual segment, like the letter H. G and I are absent respectively before and after it.

esProc approach：

A1: Execute the SQL statement.

A2: Align RGB_VALUE field in A1 with the alphabet. Below is a selection of the result:

A3：Group A2’s data. Group null values in RBD_VALUE together and place other non-null values into one group. @o means merge-style data grouping. That is, only comparing the neighboring values. The result is as follows:

A4: Select the non-null members.

A5: Compose strings according to the number of members in each group. ~.m(-1) means getting the last record from the current group. The result is as follows:

A6：With commas being delimiters, concatenate strings to get the final result A-F,H,J-K,N-O,Q-S,U-V,X-Z.

Actually we simply need to retrieve data from RBD_VALUE field. Thus the lines of code can be combined into one:

myDB1.query("select RBD_VALUE from rbd_dtl").(RBD_VALUE).align(26.(char(64+~))).group@o(!~).select(~(1)).(~(1)+if(~.len()>1,"-"+~.m(-1))).string@d()