esProc Divides Letters into Segments

Uncategorized 969 0

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:

  A
1 =myDB1.query(“select * from rbd_dtl”)
2 =A1.align(26.(char(64+~)),RBD_VALUE)
3 =A2.group@o(!RBD_VALUE)
4 =A3.select(RBD_VALUE)
5 =A4.(RBD_VALUE+if(~.len()>1, “-“+~.m(-1).RBD_VALUE))
6 =A5.string@d()

A1: Execute the SQL statement.

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

esProc_sql_letter_segment_2

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:

esProc_sql_letter_segment_3

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.

esProc_sql_letter_segment_4

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()

FAVOR (0)
Leave a Reply
Cancel
Icon

Hi,You need to fill in the Username and Email!

  • Username (*)
  • Email (*)
  • Website