SQR Masks

It seems appropriate to talk about masks with Halloween coming.  More powerful than trick-or-treat facewear, SQR masks can format numbers, analyze dates, and precisely extract characters from strings.  Boo!

Masks Appear In Many Guises

SQR helpfully integrates the mask function in many commands.  Alter-Locale allows us to configure the behavior of date and number default masks.  Concat will edit a string or date with a mask before appending it to a string variable.  Move will edit a string, date, or number with a mask before assigning it to a string variable.  Display, print, and show will edit a value with a mask before outputting it.  Input can use a mask with its format parameter for accepting dates.  The edit(), datetostr(), and strtodate() functions use masks.

Number Masks

The number masks can do more than convert numbers to strings.  If the source value has a fraction, the mask will perform the round() function according to the number of “9″ digits after the decimal point.  It can left justify or right justify the numeric string.  It can add leading spaces or leading zeroes.  With the help of alter-locale, it can use any symbol for currency and place it before or after the amount.  It can format negative numbers in a variety of styles: leading or trailing minus sign, or enclosed by parentheses or angle brackets.

The number masks do struggle with numbers that require more characters than the mask indicates.  If we use a mask like “9999″ for values like 10000 or -1000, the result will be a string of asterisks (”****”).  The mask function doesn’t even take a fail-soft option, for instance, when the mask is “9,999.99″ and the value is 10000, the result will be asterisks (”*****.**”) when it could have given us the five digits without the comma or with fewer decimal places.

The number masks can be tricked into producing treats, given our seasonal theme, although it’s barely worth the effort.  Usually, we would store Social Security numbers as nine or eleven character strings, but here’s how to do it with numbers and masks.


show 1234567 edit 099,99,9999

Note the thousand-separator can be almost any character and the edit mask still will use commas to position the thousand-separators.  Also, the thousand-separators don’t need to be three digits apart.  We can’t do the same trick with multiple decimal-separators because the edit mask will expect the entire integer part of the number to fit before the first decimal-separator.

Date And Time Masks

There are many ways to format a number; -2000 can have a comma or not, decimal places or not, a minus sign before or after the digits, parentheses or brackets instead of a minus sign, a different color (red for negatives), roman numerals (if Romans had understood negative numbers), or exponential notation.  Since we’re programmers, I’ll also mention octal and hexadecimal bases.

That’s nothing – NOTHING – compared to the variation in date and time formatting.  SQR date masks recognize the individual parts of date and time (year, month, day, hour, minute, and second) and allow us to put them in any order with almost any punctuation.  The masks also recognize the various forms that each part can take.  We might want the two, three, or four last digits of the year.  We might want the number of the month as a Hindu-Arabic numeral or Roman numeral.  We might want the name or abbreviation of the month, in upper, lower, or proper case.  We might want the hour from the 12 hour clock or the 24 hour clock.

The masks go beyond formating to give us the properties of a date.  What day of the week is it (number or name)?  What day of the month is it?  What day of the year is it (1 – 366)?  What day since January 1, 4713 (ask Joseph Scaliger)?  What week of the month is it?  What week of the year?  What quarter of the year is it?  What phase of the moon is it?  (Perhaps that last question will be answered in SQR version 10.)  This is one of the delightful quirks of SQR.  A function that seems to be a string-handling feature includes a comprehensive date-oriented mathematical subroutine library.

Text Masks

It doesn’t seem necessary to format a string as a string, but text masks can replace elaborate formulae that use substr().  Let’s start with Social Security numbers.  Peoplesoft stores the nine digits in a character field.  To print it, we could use the following example.  The nine characters map into the nine “X”, with the dashes inserted as shown.

print &NATIONAL_ID (+1,1) edit XXX-XX-XXXX

If we import Social Security numbers (including dashes) from an external source and wish to load them into PS_PERS_NID, we would use the following mask.  The “~” (tilda) tells SQR to skip the character in that position.

let $national_id = edit($import_value, ‘XXX~XX~XXXX’)

Is easier to write than

let $national_id = substr($import_value, 1, 3) || substr($import_value, 5, 2 ) || substr($import_value, 8, 4)

We can also use text masks to combine print statements.  Here’s one example.

print ‘Employee ‘ (+1,1)
print &EMPLID ()
print ‘ is scheduled for an appraisal this month.’ ()


print &EMPLID (+1,1) edit ‘Employee XXXXXX is scheduled for an appraisal this month.’

Take care that the static text in the edit mask isn’t an “insert-value” character, like this.

print $current-date (+1,1) edit ‘Report Date: Month dd, yyyy’

The “D” in the word “Date” will be replaced with the day of the month.  To avoid that, preface the “D” with a backslash.

print $current-date (+1,1) edit ‘Report \Date: Month dd, yyyy’

Comments are closed.