I was creating a query against an Intersystems Cache DB where the fields needed to be fixed length to export to a flat file. For a field with data of length 4 and an file specification of length 10, I need to concatenate 6 spaces to the end of the data. With other systems I’ve always accomplished this using a REPEAT or REPLICATE function, calculating the amount of spaces as flat file specification length minus the field length. This query would look like this:

SELECT field || REPEAT(10 – LEN(field)) FROM Table

In Cache though, what returned was a completely empty string. Knowing NULL values can cause this, I made sure there was data in the field. I tried using different concatenate operators like || or fn{ Concatenate()} but still had the same results. After pecking around, I found a different function, SPACE(), that does exactly what I needed, adding in a specified numbers of spaces to the end of a string. I tried this in my query and finally got the expected results:

SELECT field || SPACE(10 – LEN(field)) FROM Table

I’m still not sure why REPEAT and REPLICATE were behaving like this, but I’m glad there is a viable option.